How to aggregate data from SQL Server 2005

Question

I have about 150,000 rows of data written to the database daily. For example, these lines represent outgoing articles. Now I need to show the graph using SSRS , which show the average number of articles per day over time . I also need to have information on the actual number of articles since yesterday .

The idea is to have an aggregated view of all our transactions and have something that may indicate that something is wrong (for example, we send 20% fewer articles than average).

My idea is that daily data is moved to SSAS every night and keep the aggregated value of the number of transactions and the actual amount of the transaction from yesterday's data. Using SSAS will hopefully speed up reports.

Do you think this is the right idea? Should I skip SSAS and receive reports on raw data? I know how to use Reporting Services for raw data using standard SQL queries, but how will this change with an SSAS query? I do not know SSAS - where to start ??

Answer 1

SSAS , , , , KPI.

" - Microsoft SQL Server 2005" . , , , , . SSAS, OLAP , .

blog SSAS MDX .

, Microsofts Online.

Answer 2

, SSAS ( Analysis Services) SSIS (?)?

SSAS ETL, OLAP.

SSIS - ETL.

, . .

Answer 3

SSAS - ETL. - ( ), - () ( , ..). .

, , , - . , , , . .

TL; DR: . SSAS . . : .

Answer 4

@Sergio @Rowan

, (, SSIS). .

Answer 5

@Riri, , SSAS , . , , SQL Server T-SQL script.

I used this approach for several years in a daily process to calculate business metrics with approximately 9 GB of new data per day. It works, quickly, simply, and uses technology that you are used to. If your daily process gets complicated (you need to read it from files, use FTP, send emails), you can upgrade to the SSIS package (or any other ETL tool you like), but I cannot recommend using SSAS if you do not need to Provide OLAP features to your users.