locked
Many to Many with Date effective requirement RRS feed

  • Question

  • Hi all,

    I have a scenario which I am not sure how to approach this. Let me explain with sample data.

    I have an Item Table

    Item#

    ItemName

    Item1

    ItemName1

    Item2

    ItemName2

    Item3

    ItemName3

    I have a Classificaton Table

    Clasification#

    ClassificationName

    Classification1

    ClassificationName1

    Classification2

    ClassificationName2

    Classification3

    ClassificationName3

    I have an ItemClassification Table

    Item#

    Classification#

    StartDate

    EndDate

    Item1

    Classification1

    1/1/2009

    1/1/2010

    Item1

    Classification2

    1/2/2010

    1/1/2011

    Item1

    Classification3

    1/2/2011

    1/1/2012

    Item1

    Classification1

    1/2/2012

    31/12/2154

    Item2

    Classification1

    1/1/2009

    1/1/2010

    Item2

    Classification3

    1/2/2010

    31/12/2154

    Item3

    Classification3

    1/2/2011

    1/1/2012

    Item3

    Classification1

    1/2/2012

    31/12/2154

    I have a SalesTrans Table

    SalesOrder#

    SalesAmount

    TransDate

    ItemId

    SO1

    100

    1/1/2009

    Item1

    SO1

    234

    1/2/2010

    Item2

    SO2

    345

    1/2/2011

    Item3

    SO2

    456

    1/2/2012

    Item1

    SO2

    567

    1/1/2009

    Item2

    SO3

    234

    1/2/2010

    Item2

    SO3

    345

    1/2/2011

    Item1

    SO3

    343

    1/2/2012

    Item3

    SO4

    234

    1/1/2009

    Item2

    SO4

    765

    1/2/2010

    Item1

    SO5

    567

    1/2/2011

    Item3

    SO5

    345

    1/2/2012

    Item2

    SO5

    234

    1/1/2009

    Item1

    I have a Date Table as well (Not giving sample data, but I guess that would not be necessary)

    Now here is what is required:

    SalesTrans Measure Group

    This can be directly done one Sales Trans Table.

    Measure:  Sales Amount, Count

    Item Dimension

    This can be done directly on Item Table

    Attributes : Item#, Description

    Classification Dimension

    This can be done directly on Classification Table

    Attributes: Classification #, Descrption

    Date Dimension

    This can be done directly on Date Table

    Attributes: Date, Week, Month. Half year,Year, Day of month, Day of Week etc.

    The Dimension Item and Date can have Regular Dimensional Usage wth Fact.

    The classification can also slice fact table with Many-to-Many usage using the ItemClassifcation table as a fact less fact.

    But the requirement is that the effective date should also apply for example if I have classification on rows and a date filter applied (which covers date for 2 classifications for item) then I should data something like this (Data just sample):

    StartDate

    1/1/2009

    EndDate

    1/1/2012

    Item #

    Classification#

    SalesAmount

    Item1

    Classification1

    500

    Item2

    Classification2

    3000

    Item3

    Classification3

    4000

    How do I approach this? Any suggestion would be helpful

    - Girija

    Please mark responses as answered if it helped you.. This helps others... - Girija Shankar Beuria

    Saturday, August 9, 2014 7:03 AM

Answers

All replies