Shrunken Dimension: Model and loading

View previous topic View next topic Go down

Shrunken Dimension: Model and loading

Post  jldosil on Wed Aug 05, 2015 10:21 am

Hello:

We have the following model:
Fact_Order->Dim_Item

Dim_Item contains the attributes Item,Subcategory and Category

Now we have to load two new facts:
Fact_Budget: Category level
Fact_Invoice: SubCategory Level

What we have to do? Create two new dimensions Dim_Category and Dim_Subcategory from Dim_Item? We have to repeat the values of category on Dim_Subcategory for example?

Thanks


jldosil

Posts : 3
Join date : 2011-09-19

View user profile

Back to top Go down

Re: Shrunken Dimension: Model and loading

Post  ngalemmo on Wed Aug 05, 2015 3:38 pm

Invoicing is typically at the line level, which is usually item. If you are aggregating the source at the sub category level, I would suggest you don't.

But if that is what you have, then yes, you would create category and sub category dimensions. I would populate category info in the sub category 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: Shrunken Dimension: Model and loading

Post  jldosil on Fri Aug 07, 2015 5:16 am

Thank you ngalemmo.

Our invoicing System donīt record transactions at Item level. We always load the maximum grain available (thank you "The Data Warehouse Toolkit")

Another question, our BI tool recommends (God knows why) a snowflake schema (in fact modeling a star schema is more complicated in the tool).

Have any sense to you, loading the dimensions in snowfalke style, (i mean, Dim_Item only contains Item and surrogate key to Dim_Subcategory that contais only Subcategory info and surrogate key to Dim_Category...), and then create materialized views for resolving the "star dimensions". I mean, the joins with the fact always be through the materialized views (only a join) due to query rewriting.

Is that still a Star Schema? Its just an idea, i donīt know if it make sense.

Thanks again.


Last edited by jldosil on Fri Aug 07, 2015 11:44 am; edited 1 time in total

jldosil

Posts : 3
Join date : 2011-09-19

View user profile

Back to top Go down

Re: Shrunken Dimension: Model and loading

Post  sharvan.kumar.83@gmail.co on Fri Aug 07, 2015 11:18 am

if you go with create two Dimensions from the d_item.. than your d_item will be role playing . I think you create a view for category and subcategory and use that in your respective fact tables.

and that would be still start schema.


sharvan.kumar.83@gmail.co

Posts : 10
Join date : 2014-11-17

View user profile

Back to top Go down

Re: Shrunken Dimension: Model and loading

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

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