1

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?

3 Answers 3

3

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!

4

You're really asking the wrong questions. The big ones are “Does it work currently?” and “Does it produce results rapidly enough?” If they're both true, it's going to be a terribly hard sell to change to anything else, even if it happens to be more efficient to do it some other way. That's because any change has risk; in particular, the risk of someone doing something stupid and breaking things by accident. To change anything, you have to show that the benefit of changing outweighs the inevitable risk (bearing in mind that most people are not very good at understanding risk in the first place, and that not all risk is monetary).

As for why this sort of solution was used in the first place, it might be that this was simply technology that the developers knew they could make work in a reasonable amount of time, or it might actually be advantageous in some way that you don't know about yet. Your first step needs to be to find out what the history of the system is.

1
  • Thanks for the response but I'm really looking for advice directly related to SSAS Cubes and MDX rather than general advice.
    – programmer
    Jun 14, 2012 at 23:02
2

Is that overkill? Based on the information you have provided, I would say probably yes. Given the table is relatively small and it seems the users are mostly interested in a limited number of "canned" reports, it might have been simpler just to deliver the reports using SSRS and stored procedures running directly on the table.

In my experience cubes are more valuable when you have a large number of users who want to create their own reports, drill down, slice the data in different ways etc. With very large data sets there can also sometimes be performance advantages with cubes. Also, some types of queries are more easily expressed using MDX.

On the other hand sometimes there is so clear right or wrong way to do something, so I wouldn't lose any sleep over it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.