<p>I have about 150 000 rows of data written to a database everyday. These row represent outgoing articles for example. Now I need to <strong>show a graph using <a href="http://www.microsoft.com/technet/prodtechnol/sql/2005/2005ssrs.mspx" rel="nofollow noreferrer">SSRS</a> that show the average number of articles per day over time</strong>. <strong>I also need to have a information about the actual number of articles from yesterday</strong>.</p>

<p>The idea is to have a aggregated view on all our transactions and have something that can indicate that something is wrong (that we for example send out 20% less articles than the average).</p>

<p>My idea is to have yesterdays data moved into <a href="http://www.microsoft.com/sql/technologies/analysis/default.mspx" rel="nofollow noreferrer">SSAS</a> every night and there store the aggregated value of number of transactions and the actual number of transaction from yesterdays data. Using SSAS would hopefully speed up the reports.</p>

<p><strong>Do you think this is the right idea? Should I skip SSAS and have reports straight on the raw data? I know how use reporting services on raw data using standard SQL queries but how would this change when querying SSAS? I don't know <a href="http://www.microsoft.com/sql/technologies/analysis/default.mspx" rel="nofollow noreferrer">SSAS</a> - where do I start ..?</strong></p>

[{'answer_id': 29705, 'author': 'Rowan', 'author_id': 2087, 'author_profile': 'https://Stackoverflow.com/users/2087', 'pm_score': 0, 'selected': False, 'text': '<p>SSAS is an <a href="http://en.wikipedia.org/wiki/Extract,_transform,_load" rel="nofollow noreferrer">ETL</a> tool. Basically you get data from somewhere (your outgoing articles), do something to it (aggregate), and put it somewhere else (your aggregates table, data warehouse, etc). Check the link for details. </p>\n\n<p>You probably won\'t be keeping all of the rows in the DB indefinitely and if you want to be able to report on longer trends you need in any case do some kind of aggregating of historical data. So making the reports use this historical data store as their source makes sense. You can then use it to do all kinds of fancy reporting.</p>\n\n<p>TL;DR: Define your aggregated history table with your future reporting needs in mind. Use the SSAS to populate the table and refresh it from the daily updates. Report from that table. Further reading: Star Schemas and data warehousing.</p>\n'}, {'answer_id': 29769, 'author': 'Sergio Acosta', 'author_id': 2954, 'author_profile': 'https://Stackoverflow.com/users/2954', 'pm_score': 1, 'selected': False, 'text': "<p>Are you sure you aren't mixing up SSAS (Analysis Services) and SSIS (integration services)?</p>\n\n<p>SSAS is not an ETL, it is an OLAP tool.</p>\n\n<p>SSIS is an ETL tool.</p>\n\n<p>I agree with everything that Rowan said. I'm just confused by the terms.</p>\n"}, {'answer_id': 29784, 'author': 'Mats Fredriksson', 'author_id': 2973, 'author_profile': 'https://Stackoverflow.com/users/2973', 'pm_score': 3, 'selected': True, 'text': '<p>The neat thing with SSAS is that you can get those indicators that you talk about quite easily either by creating calculated measures or by using KPIs.</p>\n\n<p>I started with <a href="https://rads.stackoverflow.com/amzn/click/com/0072260904" rel="nofollow noreferrer" rel="nofollow noreferrer">Delivering Business Intelligence with Microsoft SQL Server 2005</a>. It had some good introduction, but unfortunately it\'s too verbose when it comes to the details. But if you want to understand SSAS, OLAP and reporting using this framework it\'s a good start.</p>\n\n<p>Mosha Pasumansky has a <a href="http://sqlblog.com/blogs/mosha/default.aspx" rel="nofollow noreferrer">blog</a> on SSAS and <a href="http://en.wikipedia.org/wiki/Multidimensional_Expressions" rel="nofollow noreferrer">MDX</a> with great <a href="http://www.mosha.com/msolap/" rel="nofollow noreferrer">links</a>.</p>\n\n<p>Other than that I would recommend Microsofts Online books.</p>\n'}, {'answer_id': 29891, 'author': 'Riri', 'author_id': 298, 'author_profile': 'https://Stackoverflow.com/users/298', 'pm_score': 0, 'selected': False, 'text': "<p>@Sergio and @Rowan</p>\n\n<p>Yes, we're <strong>not</strong> talking about loading and transforming data into the database (like a SSIS tool would do). That's solved using our integration platform.</p>\n"}, {'answer_id': 10416547, 'author': 'Gerardo Lima', 'author_id': 394042, 'author_profile': 'https://Stackoverflow.com/users/394042', 'pm_score': 0, 'selected': False, 'text': "<p>@Riri maybe SSAS is overkill for the situation you presented. If you only need to daily populate sumarization tables, you can accomplish it by creating a regular JOB in SQL Server and doing it in a regular T-SQL script.</p>\n\n<p>I've used this approach for several years in a daily process to calculate business indicators from about 9GB new data / day. It works, it's fast, it's simple and it uses a technology you're already used to. If your daily process get's more complicated (it needs to read from files, use FTP, send emails) you can move to a SSIS package (or any other ETL tool you like), but I cannot recommend using SSAS unless you need to provide OLAP capabilities to your users.</p>\n"}]
['https://Stackoverflow.com/questions/29678', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/298/']