Issue concerning transposing a wide table

View previous topic View next topic Go down

Issue concerning transposing a wide table

Post  CNP on Thu Jul 03, 2014 1:42 pm

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:

ProductNoProfit10p  Profit20p  Profit30p  Marg10p  Marg20p  Marg30p  Price  
1203045122233200
2253550172738205
3304055223243210
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:

ProductUnikNoProductNoPriceProfitMargPct
1101200201210
1201200302220
1301200453330
1102205251710
1202205352720
1302205503830
1103210302210
1203210403220
1303210554330
What im thinking about:

a fact
D_Pro_sur_Key
ProductNo
Price
Profit
Marg
PCT
and a dimension
D_Product
D_Pro_sur_Key
ProductNo
ProductUnikNo
Profit(containing 10-30)
Marg (containing 10-30)
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?

CNP

Posts : 1
Join date : 2014-07-03

View user profile

Back to top Go down

Re: Issue concerning transposing a wide table

Post  ngalemmo on Thu Jul 03, 2014 4:29 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Issue concerning transposing a wide table

Post  nick_white on Thu Jul 03, 2014 5:11 pm

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

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Issue concerning transposing a wide table

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top


 
Permissions in this forum:
You cannot reply to topics in this forum