Issue concerning transposing a wide table
3 posters
Page 1 of 1
Issue concerning transposing a wide table
Hi,
I am a newbie in this dim. model world, and on my path to master this skill I have encountered the following issue.
I have made a example with fictional data, so dont look at the data but more the concept.
I have this start table which is need to be converted into a dimensional model, so the users has access to an analytical table.
Start/input table:
In the real example there will be 20 or 30 Profit* and Marg* columns. And its more complex with time, country and other dimensions, but my issue is concerning the Profit and Marg columns, what to do when there are so many.
What the user wants:
The user wants an analytic table where the rows profit and Marg are transposed so the table is more vertical and slim than wide and horizontal. This is want they want, and I cant change it.
A table like this:
What im thinking about:
a fact
and a dimension
D_Product
But i cant make it work...
Any other suggesting on how to handle this? I dont think the design is good. I can change the design, but I cant change what the user wants.
Are there any relevant chapters in Kimballs books which address this?
I am a newbie in this dim. model world, and on my path to master this skill I have encountered the following issue.
I have made a example with fictional data, so dont look at the data but more the concept.
I have this start table which is need to be converted into a dimensional model, so the users has access to an analytical table.
Start/input table:
ProductNo | Profit10p | Profit20p | Profit30p | Marg10p | Marg20p | Marg30p | Price |
1 | 20 | 30 | 45 | 12 | 22 | 33 | 200 |
2 | 25 | 35 | 50 | 17 | 27 | 38 | 205 |
3 | 30 | 40 | 55 | 22 | 32 | 43 | 210 |
What the user wants:
The user wants an analytic table where the rows profit and Marg are transposed so the table is more vertical and slim than wide and horizontal. This is want they want, and I cant change it.
A table like this:
ProductUnikNo | ProductNo | Price | Profit | Marg | Pct |
110 | 1 | 200 | 20 | 12 | 10 |
120 | 1 | 200 | 30 | 22 | 20 |
130 | 1 | 200 | 45 | 33 | 30 |
110 | 2 | 205 | 25 | 17 | 10 |
120 | 2 | 205 | 35 | 27 | 20 |
130 | 2 | 205 | 50 | 38 | 30 |
110 | 3 | 210 | 30 | 22 | 10 |
120 | 3 | 210 | 40 | 32 | 20 |
130 | 3 | 210 | 55 | 43 | 30 |
a fact
D_Pro_sur_Key |
ProductNo |
Price |
Profit |
Marg |
PCT |
D_Product
D_Pro_sur_Key |
ProductNo |
ProductUnikNo |
Profit(containing 10-30) |
Marg (containing 10-30) |
Any other suggesting on how to handle this? I dont think the design is good. I can change the design, but I cant change what the user wants.
Are there any relevant chapters in Kimballs books which address this?
CNP- Posts : 1
Join date : 2014-07-03
Re: Issue concerning transposing a wide table
Vertical is fine. Its the best way to handle this.
A fact table has measures and context (dimensions). From what I can see, the measures are price, profit, and margin. It's not clear what the PCT column represents, it's either a measure or a derived (calculated value).
As for dimensions, Product is one. It should just contain information about the product. You need another dimension that characterizes the measures on the row. Basically information about the cell that has been broken out, which may the the prof and marg stuff you are trying to put into the product dimension.
A fact table has measures and context (dimensions). From what I can see, the measures are price, profit, and margin. It's not clear what the PCT column represents, it's either a measure or a derived (calculated value).
As for dimensions, Product is one. It should just contain information about the product. You need another dimension that characterizes the measures on the row. Basically information about the cell that has been broken out, which may the the prof and marg stuff you are trying to put into the product dimension.
Re: Issue concerning transposing a wide table
Just to throw in my opinion, for what it's worth...
A dimensional model is used for delivering reports - you create a fact table, join it to dimensions and then aggregate the measures in the fact grouped by attributes in your dimensions to produce your report.
However, your user seems to be asking you to produce a table (not a report) that effectively dumps the content of your fact table out to another table (you have the same number of records in your output table as you have in your fact table). You describe this output as an analytical table so presumably your user is then going to use this data in some subsequent process?
In my opinion either you use the dimensional model to deliver the user's complete complete reporting requirements (not just a table used as part of the reporting/analysis process) or you take this out of your dimensional model entirely. If you have the source data as you describe and you want to convert it to the target structure then do it using SQL, PL/SQL, an ETL tool or code - trying to do it via a dimensional model doesn't seem to be adding anything and is using the DM for something that it's not really designed for
A dimensional model is used for delivering reports - you create a fact table, join it to dimensions and then aggregate the measures in the fact grouped by attributes in your dimensions to produce your report.
However, your user seems to be asking you to produce a table (not a report) that effectively dumps the content of your fact table out to another table (you have the same number of records in your output table as you have in your fact table). You describe this output as an analytical table so presumably your user is then going to use this data in some subsequent process?
In my opinion either you use the dimensional model to deliver the user's complete complete reporting requirements (not just a table used as part of the reporting/analysis process) or you take this out of your dimensional model entirely. If you have the source data as you describe and you want to convert it to the target structure then do it using SQL, PL/SQL, an ETL tool or code - trying to do it via a dimensional model doesn't seem to be adding anything and is using the DM for something that it's not really designed for
nick_white- Posts : 364
Join date : 2014-01-06
Location : London
Similar topics
» very large/wide fact table considerations?
» Wide and large Dimension or Survey Factless Fact Table
» Model not-fixed-step for process in a wide datawarehouse. Extend fact-table ?
» Grain issue in the fact table
» Bridge table and double counting issue
» Wide and large Dimension or Survey Factless Fact Table
» Model not-fixed-step for process in a wide datawarehouse. Extend fact-table ?
» Grain issue in the fact table
» Bridge table and double counting issue
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum