During my internship, I had to create a cube for sales based on only one dB (of an ERP).
So I've created a view grouping information I need.
So I've only one "table" (a view actually) which is my fact table and my only dimension table.
Actually, the dimension are the attributes of my sole dimesion.
The problem is that I've to write my report and I don't know if I've made the good choice. I can also create a dimension table with Customer, for countries ... but the result would be the same.
Thanks in advance for your advices.
Hey there,
If you are happy with the performance, the SQL is maintainable and you will only ever have one cube for your company, then your solution will be adequate.
However, you may find that you run into performance, scalability and maintenance issues as your data grows and if you need to start working with other business processes (e.g. order processing or invoicing).
By having a single view you are bypassing some of the automated work a cube / Analysis Services can give you. Also with the view, you may end up replicating some logic for bringing back dimension values (e.g. country). If you were to have another view for a new cube, you would have to replicate all the SQL for extracting the country information to return in your flattened view.
By using dedicated dimension tables, you will gain re-use of dimension content (conformed dimensions), requiring only a single extract from the source system.
There are numerous other benefits of having a more structured data warehouse/data extract process, such as change tracking, referential integrity checking, introduction of surrogate keys (non source system dependent keys if you are dealing with multiple data sources).
Hope that made sense,
Jonathon
No comments:
Post a Comment