none
Get dimensions from transactional table

    Question

  •  How do I get dimensions from transactional table?

    In transactional table/datasourceview i have SKU , model, color, size, brand ,qty, date - and I want to make  dimensions from model, color, size, brand.
    How do I extract dimensions from flat transactional table? What should I specify for Primary-foreign keys to relate new dimension back to fact table?

    I searched a lot but could not find step by step instructions. Any help is much appreciated
     
    • Edited by BrBa Saturday, November 09, 2013 4:50 AM
    Saturday, November 09, 2013 4:47 AM

Answers

  • Opt 3 - I want to try this option first. Question here - I'm curious Will SSAS work LIKE  Excel pivot table with flat Excel spreadsheet? Meaning, imagine, you have flat Excel spreadsheet, some columns have repetitive values (like ,model, size, brand) and when you "insert Pivot table", Excel will automatically show distinct model, size, brand values as Pivot table fields (that would be similar to dimensions). Can SSAS engine handle this scenario?

    Hello,

    When we define a dimension based on such a fact table item, the dimension is called a fact dimension. Fact dimensions are also known as degenerate dimensions. To implement this, please refer to the articles below:
    Creating Degenerated dimension: http://sql-bi-dev.blogspot.in/2010/05/creating-degenerated-dimension.html
    Defining a Fact Relationship: http://technet.microsoft.com/en-us/library/ms167409.aspx

    Yes. Dimensions in Analysis Services contain attributes that correspond to columns in dimension tables,  and the attribute members represent the unique values stored in the underlying attribute column. For example, if we want the end user doesn't see repeating colors when browsing by the Color attribute, we need to work with the following attribute properties:

    • KeyColumns(required)- This is the most important attribute binding. KeyColumns identifies uniquely the attribute members by one or more columns.
    • NameColumn(Optional)- Provides the visual representation of the attribute members. If not specified, the key column will be used.
    • ValueColumn(Optional)-Designates which column will provide the member value. If not specified, the key column will be used.

    For more information about Dimension Attribute Properties Reference, please see:
    http://technet.microsoft.com/en-us/library/ms174919.aspx

    Regards,


    Elvis Long
    TechNet Community Support

    • Marked as answer by BrBa Tuesday, November 12, 2013 1:06 AM
    Monday, November 11, 2013 7:38 AM
  • Hello BrBa,

    As I mentioned, Option3 is not the best from modelling view so I strongly suggest you to use Option 1 because if your transactional table grows with few business entities then Degenerated may have performance hit so consider regular dimension.

    Option 1:

    1. First you need to load all of your distinct values in one dimension with SK (Identity column)

    2. After completing the first load, you can have a physical dimension table

    3. Now process you transactional table. Use Lookup to previously loaded dimension table using its business keys i.e. in this case direct values and retrieve the SID of dimensions

    4. In the final fact table, you will end up with SID (Integer) values in Fact

    5. Use this in you SSAS solution to make the relations

    In SSAS

    1. Create your dimension

    2. Create your FACT

    Use Dimension SID and FACT FK as relation

    I think, this will be useful


    Prav

    • Marked as answer by BrBa Tuesday, November 12, 2013 1:04 AM
    Tuesday, November 12, 2013 12:36 AM

All replies

  • Hi

    My understanding, this is more related to business rather than technical only.

    Option 1 : Every business will have a business entities defined before making any transactions

    In this case,

    1. you can design your dimension from business entity table

    2. You can use the Business Keys from transactions to get the Primary Key (Surrogate Keys) into your FACT table

    Option 2 : In case, if you don't have any defined entity tables

    1. You can simply create a dimension while loading transaction table

    2. Insert every new record into this dimension table before loading fact table

    3. You can use the previously processing dimension as Lookup to fact table processing to get the surrogate keys

    Option 3 : Not the best solution and I never to this

    Use degenerated dimension approach to link the FACT table to itself

    Let me know your decision


    Prav

    Saturday, November 09, 2013 9:11 AM
  • Hi Prav, many thanks for your response, I understood it :) Few questions though....

    So Opt 1 means using Named Queries in Datasource View? For example, for 'MODEL' dimension the Named Query  will look like "select distinct MODEL, MODEL from TransctionalTable"? and then use first MODEL field as PK (Key Column) and second 'MODEL' field as Name column (in Dimension Wizard)? Is this correct? I'm concerned that if I keep text-like 'Model" field as a FK in Fact and in Dimension , the Text key will slow down my cube.... Is that a correct assumption?

    Opt 3 - I want to try this option first. Question here - I'm curious Will SSAS work LIKE  Excel pivot table with flat Excel spreadsheet? Meaning, imagine, you have flat Excel spreadsheet, some columns have repetitive values (like ,model, size, brand) and when you "insert Pivot table", Excel will automatically show distinct model, size, brand values as Pivot table fields (that would be similar to dimensions). Can SSAS engine handle this scenario?






    • Edited by BrBa Sunday, November 10, 2013 1:32 PM
    Saturday, November 09, 2013 8:41 PM
  • Opt 3 - I want to try this option first. Question here - I'm curious Will SSAS work LIKE  Excel pivot table with flat Excel spreadsheet? Meaning, imagine, you have flat Excel spreadsheet, some columns have repetitive values (like ,model, size, brand) and when you "insert Pivot table", Excel will automatically show distinct model, size, brand values as Pivot table fields (that would be similar to dimensions). Can SSAS engine handle this scenario?

    Hello,

    When we define a dimension based on such a fact table item, the dimension is called a fact dimension. Fact dimensions are also known as degenerate dimensions. To implement this, please refer to the articles below:
    Creating Degenerated dimension: http://sql-bi-dev.blogspot.in/2010/05/creating-degenerated-dimension.html
    Defining a Fact Relationship: http://technet.microsoft.com/en-us/library/ms167409.aspx

    Yes. Dimensions in Analysis Services contain attributes that correspond to columns in dimension tables,  and the attribute members represent the unique values stored in the underlying attribute column. For example, if we want the end user doesn't see repeating colors when browsing by the Color attribute, we need to work with the following attribute properties:

    • KeyColumns(required)- This is the most important attribute binding. KeyColumns identifies uniquely the attribute members by one or more columns.
    • NameColumn(Optional)- Provides the visual representation of the attribute members. If not specified, the key column will be used.
    • ValueColumn(Optional)-Designates which column will provide the member value. If not specified, the key column will be used.

    For more information about Dimension Attribute Properties Reference, please see:
    http://technet.microsoft.com/en-us/library/ms174919.aspx

    Regards,


    Elvis Long
    TechNet Community Support

    • Marked as answer by BrBa Tuesday, November 12, 2013 1:06 AM
    Monday, November 11, 2013 7:38 AM
  • Thank you! As per your response re "if we want the end user doesn't see repeating colors when browsing by the Color attribute, we need to work with the following attribute properties: KeyColumns(required)- This is the most important attribute binding. KeyColumns identifies uniquely the attribute members by one or more columns. "

    Can I use Color column text values (Red, Blue, Brown) as KeyColumns and omit Name/Value columns?

    Monday, November 11, 2013 3:19 PM
  • Hello BrBa,

    As I mentioned, Option3 is not the best from modelling view so I strongly suggest you to use Option 1 because if your transactional table grows with few business entities then Degenerated may have performance hit so consider regular dimension.

    Option 1:

    1. First you need to load all of your distinct values in one dimension with SK (Identity column)

    2. After completing the first load, you can have a physical dimension table

    3. Now process you transactional table. Use Lookup to previously loaded dimension table using its business keys i.e. in this case direct values and retrieve the SID of dimensions

    4. In the final fact table, you will end up with SID (Integer) values in Fact

    5. Use this in you SSAS solution to make the relations

    In SSAS

    1. Create your dimension

    2. Create your FACT

    Use Dimension SID and FACT FK as relation

    I think, this will be useful


    Prav

    • Marked as answer by BrBa Tuesday, November 12, 2013 1:04 AM
    Tuesday, November 12, 2013 12:36 AM
  • Thank you Prav, I'll do proper ETL, and thank you for the hints on ETL processs!
    Tuesday, November 12, 2013 1:05 AM