March 3, 2010 at 11:06 am
I recently joined a small group supporting a datamart for a specific line of business within a bank. A lot of time and effort (mostly preceding my arrival) has been spent implementing an SSAS cube for a particularly important subset of data. One of the developers has designed and implemented a star schema with ten dimensions for this data. From this schema, we can (and do) easily develop reports.
However, there has been a lot of debate regarding the necessity of creating a cube from the star schema. The thought is that our reports will run much faster if they query a cube. I've been adamant arguing against this because our two fact tables have only 14k and 120k records and the cube doesn't provide any other important benefits. I wouldn't consider a cube until our fact tables approach ~1,000,000 records.
Before I start banging the table on this point at our weekly team meetings, is there anything I'm missing? Are there other considerations that I'm ignoring?
Thanks in advance.
March 3, 2010 at 1:59 pm
The answer is: it depends 🙂
If the reports requires data that is the result of very complex operations, I would consider a cube, since the data is precalculated over night. You can then optimize the queries that the reports use with aggregations.
If the reports show only simple aggregations of the fact tables, you can achieve fast queries if your tables are well-designed and if the right indexes are constructed.
Since the fact tables are quite small, I think it is possible without a cube.
But a cube has some other advantages:
* what if your fact tables grow over time? Cubes will process longer, but normally this is out of office hours, so it is not a problem. Reports without a cube will run slower.
* what if your business users want to analyze the data with a tool like Cognos Analysis Studio (I'm familiar with this tool only, so that's why I use it in this example. Simply put, Analysis Studio is similar to the browser in SSAS)? Such tools only work on dimensional data (or relational data modeled as dimensional data in a framework).
I believe that with a cube, you can anticipate in some possible future requirements.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply