January 15, 2010 at 4:34 am
Hi,
I have some kind of SAP export where a lot of columns representing a ratio. In other words each column represents one ratio. Depending on which Dimension keys are not null these columns has data or not.
Is there a best practise way to design something like this?
I thought one way could be to create a dimension with all ratios and transform this data that I have one value column joined with the ratio dimension and additional dimensions.
Any suggestions ?
Thanks a lot
January 19, 2010 at 8:19 am
A dimension table should describe a single attribute in a fact table - is this the case?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.March 12, 2010 at 10:43 pm
hal9000-869914 (1/15/2010)
Hi,I have some kind of SAP export where a lot of columns representing a ratio. In other words each column represents one ratio. Depending on which Dimension keys are not null these columns has data or not.
Is there a best practise way to design something like this?
I thought one way could be to create a dimension with all ratios and transform this data that I have one value column joined with the ratio dimension and additional dimensions.
Any suggestions ?
Thanks a lot
Is the ratio meaningful by itself? or is it just a number? If you answer "Its just a number" than no you should not build it as a dimension. A dimension is a way of looking at the data. A dimension value by itself has meaning. A measure such as salary has no meaning by itself but the dimension value employee ID has meaning and Employee ID is a way to look at the data such as salary.
It sounds like you have "ratio types" with corresponding ratios(numbers). Also sounds like there are nulls when that ratio type does not exist there is no corresponding ratio.
My thought is you are looking at different data but in the same table. One option is to separate the data into multiple tables. When building an analysis solution these different tables would represent different measure groups. In the case where dimensions are the same in different tables you would use the same dimension key. When performing analysis you have no need to aggregate the different ratio types together so that would not cause a problem. There would be one set of dimensions shared by all tables so the user could pick a geographic location for example and see ratios common to that dimension.
If ratios are aggregated then the data would need to be un-pivoted and the multiple columns would become multiple rows.
hth - tom
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply