Dimensional Model with different granularity to Cube

[richardbadge]

Apologies if I have the terminology wrong and don't explain things very well, as I am relatively new to this and am getting confused on a design decision and just wanted some advice.

I work in the business but am working with the IT department on a design of a dimensional model and cube for a Cost value system we have. The commercial team submit a single Cost value report each month per project and this is saved in an operational database, there are loads of fields stored for all the different costs, revenue, cash at levels like labour, plant, material. I was going to have a fact table with the granularity of a single row per form submitted per month with dimensions for the month and the project, the fact table would then have different columns for every single field on the CVR and to me this made sense.

However, when speaking to people in the business they have said that when presenting this in a cube, the user will have loads of measures to select and will get very confusing on what to use and they have suggested adding additional groupings in the cube to group certain costs and value together, which to be honest makes sense as well.

What I would like to know is can you still have a dimensional model with the single fact row per CVR with all the fields and then when creating the cube put in the additional logic to add the dimension in for the further analysis at the cube level?

The reason I want to keep the dimension model at the single row per CVR as there are metrics that do not easily fit into the additional dimensions in the cube and I want to keep all of the information.

I hope this is clear.

[ngalemmo]

Yes, you can aggregate data before loading into a cube. A view is probably the easiest way to handle it.

[richardbadge]

Many thanks, I have a meeting with the designers on Friday, so will discuss this with them.

[Sponsored content]