I am new to Microsoft's SQL Server Analysis Services Cubes and MDX queries. Where I work we have a daily sales table in SQL Server 2005 that already contains an aggregate of sale information per store per day. At this time it contains only 164,000+ rows. We have a sales cube dedicated to this table that about 15 reports are based off of. Now, I should also note that we generate reports based on our own fiscal year criteria: a 13 period year (1 month equals 28 days etc.).
Is this overkill? At what point is it justified to begin using SSAS Cubes/MDX over plain old SQL Server stored procedures? Since I have always been just using plain old SQL am I tragically late to the MDX party?
Best Answer
Generally people don't go the datawarehousing route until performance is suffering on the production server. With large reports and aggregations, reporting, especially with one or more years' worth of data, can be extremely slow in an ordinary relational database. It is especially true when a report that returned only 200 records when it was created returns 2 million records in December and has 20 or 30 calcuations that end up being processed row-by row through functions. And at times running those huge reports ends up stopping eveyone else's work. So the work is moved to a data warehouse and the report people are happy becuase their reports run faster and the users are happy because they don't heve slowdowns once a month whe the reports run and the BI people are happy because they always have work to do and becasue they get paid better than someone who just writes t-sql code!