locked
Create a fact table from two Dim Tables - is it possible in powerpivot? RRS feed

  • Question

  • I have two comma delimited flat files.

    1 - Customer Details (customerCode, Name, Address etc)

    2 - Order Details(OrderNumber, OrderLineNumber, CustomerCode etc)

    The two link by CustomerCode. I can bring both into powerpivot and link them directly, so cna deliver a report quickly. This model will grow however, with product dimension, store dimension and a few others.

    So, i want to design it correctly from the outset, having a DimCustomer & a DimOrderDetail, that both link via a FactTable. I cant figure out how to create a factTable using both Dimensions using only powerPivot & dax. Is it possible?

    Friday, August 8, 2014 10:23 AM

Answers

  • Shaping the data before it gets to PowerPivot using Power Query is the easier option:

    http://office.microsoft.com/en-us/excel-help/introduction-to-microsoft-power-query-for-excel-HA104003940.aspx

    http://www.microsoft.com/en-us/download/details.aspx?id=39379

    Ideally, with Power Pivot, the best model is a star schema where you have a single fact table and all the dimensions are linked directly to the fact table and not to one another.  This isn't always possible in real world modeling but it is definitely the easiest to deal with when writing measures and designing pivots.  It also generally gives the best performance.

    • Proposed as answer by Michael Amadi Sunday, August 10, 2014 11:08 AM
    • Marked as answer by Charlie Liao Sunday, August 17, 2014 2:45 PM
    Friday, August 8, 2014 3:07 PM
    Answerer
  • Your Order Details table may already be the logical fact table.  Without knowing the specific detail of your table structure and the values that you need to aggregate, I can't give you a definite answer but I can tell you when often works in situations like this.  The conventional approach is to use a tool like SSIS or Power Query to transform the data into a fact table containing only numeric "measure base" columns and keys, and to create dimension tables that have no numeric "measure base" columns and only attributes columns and keys.  These tables are related in a star configuration and the measures are added to the fact table.

    Aside from the conventional approach, in many cases, you can just use your all-in-on tables like you have now and create a separate empty, unrelated table to store the measures.  Hide all the numeric columns in the existing tables and write your measures in this empty table by using the base columns from the other tables.  This way you have separation between the dimension tables and their attributes from the measures, residing in a separate container.

    If you have specific design questions, post a copy of your table structures, keys and the columns you need to aggregate and calculate.


    Paul Turley, MVP, www.SQLServerBIBlog.com *Please vote if helpful*

    • Proposed as answer by Michael Amadi Sunday, August 10, 2014 11:08 AM
    • Marked as answer by Charlie Liao Sunday, August 17, 2014 2:45 PM
    Sunday, August 10, 2014 6:36 AM

All replies

  • Shaping the data before it gets to PowerPivot using Power Query is the easier option:

    http://office.microsoft.com/en-us/excel-help/introduction-to-microsoft-power-query-for-excel-HA104003940.aspx

    http://www.microsoft.com/en-us/download/details.aspx?id=39379

    Ideally, with Power Pivot, the best model is a star schema where you have a single fact table and all the dimensions are linked directly to the fact table and not to one another.  This isn't always possible in real world modeling but it is definitely the easiest to deal with when writing measures and designing pivots.  It also generally gives the best performance.

    • Proposed as answer by Michael Amadi Sunday, August 10, 2014 11:08 AM
    • Marked as answer by Charlie Liao Sunday, August 17, 2014 2:45 PM
    Friday, August 8, 2014 3:07 PM
    Answerer
  • Your Order Details table may already be the logical fact table.  Without knowing the specific detail of your table structure and the values that you need to aggregate, I can't give you a definite answer but I can tell you when often works in situations like this.  The conventional approach is to use a tool like SSIS or Power Query to transform the data into a fact table containing only numeric "measure base" columns and keys, and to create dimension tables that have no numeric "measure base" columns and only attributes columns and keys.  These tables are related in a star configuration and the measures are added to the fact table.

    Aside from the conventional approach, in many cases, you can just use your all-in-on tables like you have now and create a separate empty, unrelated table to store the measures.  Hide all the numeric columns in the existing tables and write your measures in this empty table by using the base columns from the other tables.  This way you have separation between the dimension tables and their attributes from the measures, residing in a separate container.

    If you have specific design questions, post a copy of your table structures, keys and the columns you need to aggregate and calculate.


    Paul Turley, MVP, www.SQLServerBIBlog.com *Please vote if helpful*

    • Proposed as answer by Michael Amadi Sunday, August 10, 2014 11:08 AM
    • Marked as answer by Charlie Liao Sunday, August 17, 2014 2:45 PM
    Sunday, August 10, 2014 6:36 AM