none
Creating Dimension Tables RRS feed

  • Question

  • Hi guys

    I've like to know of a more efficient way to create dimension tables from a single fact table.

    My current process is:

    1. Duplicate my fact table
    2. Remove all columns except for the key column
    3. Remove duplicates
    4. Add an Index
    5. Save and load
    6. Go back to fact table and merge it with the new table above
    7. Pull across Index column

    I think I could avoid most of this overhead by simply adding an index for each unique instance of a particular record from a selected field, i.e. a create key column formula. Is this possible?  Other suggestions?

    Simon

    Friday, August 14, 2015 2:50 PM

Answers

All replies

  • Can you explain what you are trying to accomplish here?

    My first question would be why create a dimension table at all, you can create a slicer on any column in your fact table and it basically already does what you are doing with creating a dimension table...

    Friday, August 14, 2015 3:10 PM
  • Hi Seth

    I'm trying to create dimension tables to insert into a Power Pivot model.

    I currently have about 400,000 rows of data and about 40 columns.  The steps listed in my post above create a dimension table with keys that link to the fact table.  This allows me to normalize the data model thereby massively reducing any redundancies and allowing the model to process much faster.

    Monday, August 17, 2015 7:00 PM
  • Hi Simon,

    not sure if I fully understood how you currently proceed, but have a look if this is more straightforward:

    http://www.thebiccountant.com/2015/08/17/create-a-dimension-table-with-power-query-avoid-the-bug/

    Due to the different handling of case sensitivity between Power Query and Power Pivot, you should stick to your separate Index column.


    Imke Feldmann TheBIccountant.com

    Monday, August 17, 2015 8:58 PM
    Moderator