locked
Product Hierarchy Assistance RRS feed

  • Question

  • I have some data that I need to get into a cube w/ a product hierarcy using SQL Server 2000 Analysis Services and wondered how I can accomplish this given the current data layout.  I have one record for each customer profile with product level binary inidcators:

     

    Product waterfall:

    Ins
    Consumer Ins Business Ins
    ID Theft Home Auto Liability   Fraud Auto

     

    Each row of data has a field for each item with a binary indicator.

    rec# ins con id_theft home c_aut bus liab fraud b_aut custs
    1 1 0 0 0 0 1 1 1 0 100
    2 1 1 1 1 1 1 1 1 1 140
    3 0 0 0 0 0 0 0 0 0 230
    4 1 1 1 0 0 1 0 0 1 224

     

    I hope this all makes sense.  I really have no idea how to get everything to fall under insurance.  I can easily put this into a cube with an indicator for each product but want to do it correctly.

     

    Can someone kindly help get me started or point me in the right direction?

     

    Thanks!

    Tuesday, November 13, 2007 10:52 PM

Answers

  • Hi! It is possible to see this as transactions. Your customer buy a type of insurance like one Theft, one Home and one Auto at different dates or at the same date. If the customer buys all three you can enter a fact record for each which means three for a consumer. I am not sure if you have these timestamps available? Add a custmomer and aa product dimension table to this. You put a quantity column to the fact table and one time key, one customer key and one product key

     

    The other way is to see this as a snapshot, what products do each customer have at a certain date.This look like the structure you have know. In this case the products will be in the fact table as a separate column for each product. You can add a customer dimension table and a time dimension to this. Be aware of that you will have facts that is not possible to aggregate(semi additive measures) by sum. Here you add a fact record each time the product structure for a customer changes or one for eac day.

     

    HTH

    Thomas Ivarsson

     

    Wednesday, November 14, 2007 2:58 PM