none
please help me to understnad why we use same table name at 3 different places in SSAS Cube and what happens when we change table name in any of the place RRS feed

  • Question

  • Hello All,

    In Cube for measure group, the base table is actually appears at 3 different places(let me know if more places are there)

    1. in DSV, under table section.

    2. in DimensionsUsage Tab, to set the relationship b/w measure group and dimension

    3. Partitions.

    Can you please let me know the impact & how SSAS works when we change table name any of these but not other places?

    For example: If we change in DSV by replacing table with other table, but do not change in partitions or vice versa.

    Many Thanks




    • Edited by ScriptSoft Friday, October 11, 2019 8:11 AM
    Friday, October 11, 2019 8:11 AM

Answers

  • Hello Will,

    Thanks for your reply.

    lets understand by simple scenario.

    There are 2 tables in DB(SQLServer/ Oracle): xyz_db , abc_db and both have identical structure

    When we create DSV and add tables from DB, we get 2 properties along with others in a DSV table: Name & FriendlyName. Name property is actually pointer to actual table(xyz_db). And it will only change when we change the table from xyz_db to abc_db.  This can be done in DSV using "Replace Table". Whereas FriendlyName property can be change anytime and this will be used throughout the cube/model. lets take this FriendlyName as xyz_dsv.

    When we create Measure Group based on a table, the name comes from DSV and it is actually FriendlyName(xyz_dsv). But after Measure Group creation, when we set relationship with dimensions under "Dimension Usage" tab, we see actual table xyz_db not xyz_dsv while making relationships.Why it doesn't take/show FriendlyName xyz_dsv? I know this is as per Microsoft design only, but any clue why it is so?

    And then another place is partition where we see table name , which is again actual table xyz_db for partition query.

    My understanding is:

    We can use different table(Name property) while creation measure group and different for partition query. there is no design restriction.

    Table in DSV(name property) is to build measure group structure based on its columns and datatype. And table in Partition query to load data from. If partition query has less columns than the measure group, it will simply DO NOT LOAD the data for that column, but will not give error or fail.

    please correct if above understanding is wrong.

    Thanks,


    MSBI-Power BI

    • Marked as answer by ScriptSoft Thursday, October 17, 2019 3:14 AM
    Tuesday, October 15, 2019 9:00 AM
  • Hi ScriptSoft.

    Thanks for your reply.

    Your understanding is basically correct, but some knowledge about relationship should be updated like this.

    >>But after Measure Group creation, when we set relationship with dimensions under "Dimension >>Usage" tab, we see actual table xyz_db not xyz_dsv while making relationships.Why it doesn't >>take/show FriendlyName xyz_dsv? I know this is as per Microsoft design only, but any clue why it is so?

    The relationships between dimensions and fact tables are usually created before Measure Group creation, they are created in the Data source view.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by ScriptSoft Thursday, October 17, 2019 3:14 AM
    Wednesday, October 16, 2019 8:47 AM

All replies

  • In a SSAS Project => DSV you can only change the "Friendly Name" of a table, the internal used name can't be changed. In all other parts of the project the friendly name is shown; so if you change the friendly Name then you see it everywhere.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, October 11, 2019 8:34 AM
  • Hello Olaf, Many thanks for your reply.

    I understand we can change the friendly name of a object ( table or view) which will be appearing in all other area in the project. But that will be just friendly name i.e. A name which everyone will be using. But the base table will always be same irrespective of times we change friendly name.

    As per my experience, we can change the Name as well (not 'FriendlyName') which is visible in the property of table in DSV. And this actually means is changing the base table to which SSAS object is pointing to. To accomplish this, right click on the object--> click on "Replace Table"--> then click on "With Other Table.."--> to the table want to replace with--> OK.

    Hence this way, we can actually change the pointer from SSAS Object to correct DB object.

    Please correct me if wrong.

    But my question was( to make it more precise) :

    1. How does actual base table's name populates instead of name of dimension/measure in the Dimension Usage tab when we go for establishing relationship between dimensions and measures?

    2.Does total of attributes in Measure Group should match with the total of of column used in partition's query? what happen if table name changed in partition query but intact in measure group or vice versa?

    Thanks


    MSBI-Power BI

    Monday, October 14, 2019 3:59 AM
  • Hi ScriptSoft,

    Thanks for your reply.

    >>1. How does actual base table's name populates instead of name of dimension/measure in the ?>>Dimension Usage tab when we go for establishing relationship between dimensions and measures?

    The only way of showing actual base table's name is to recreate data source view. At first, once you create the data source view, import dimension tables, fact tables into views. The friendly names of all tables are same as the actual base table names. Because cubes and dimensions are all created based on the tables of data source views.

    >>2.Does total of attributes in Measure Group should match with the total of of column used in >>partition's query? what happen if table name changed in partition query but intact in measure >>group or vice versa?

    There is no specific condition requirement, In general, the total of columns used in partition's query is greater than or equal to  the total of attributes in Measure Group.  I don't think the two would impact each other. Table name in partition query is actual base table name. But the one in measure group is friendly name from data source view.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, October 14, 2019 8:56 AM
  • Hello Will,

    Thanks for your reply.

    lets understand by simple scenario.

    There are 2 tables in DB(SQLServer/ Oracle): xyz_db , abc_db and both have identical structure

    When we create DSV and add tables from DB, we get 2 properties along with others in a DSV table: Name & FriendlyName. Name property is actually pointer to actual table(xyz_db). And it will only change when we change the table from xyz_db to abc_db.  This can be done in DSV using "Replace Table". Whereas FriendlyName property can be change anytime and this will be used throughout the cube/model. lets take this FriendlyName as xyz_dsv.

    When we create Measure Group based on a table, the name comes from DSV and it is actually FriendlyName(xyz_dsv). But after Measure Group creation, when we set relationship with dimensions under "Dimension Usage" tab, we see actual table xyz_db not xyz_dsv while making relationships.Why it doesn't take/show FriendlyName xyz_dsv? I know this is as per Microsoft design only, but any clue why it is so?

    And then another place is partition where we see table name , which is again actual table xyz_db for partition query.

    My understanding is:

    We can use different table(Name property) while creation measure group and different for partition query. there is no design restriction.

    Table in DSV(name property) is to build measure group structure based on its columns and datatype. And table in Partition query to load data from. If partition query has less columns than the measure group, it will simply DO NOT LOAD the data for that column, but will not give error or fail.

    please correct if above understanding is wrong.

    Thanks,


    MSBI-Power BI

    • Marked as answer by ScriptSoft Thursday, October 17, 2019 3:14 AM
    Tuesday, October 15, 2019 9:00 AM
  • Hi ScriptSoft.

    Thanks for your reply.

    Your understanding is basically correct, but some knowledge about relationship should be updated like this.

    >>But after Measure Group creation, when we set relationship with dimensions under "Dimension >>Usage" tab, we see actual table xyz_db not xyz_dsv while making relationships.Why it doesn't >>take/show FriendlyName xyz_dsv? I know this is as per Microsoft design only, but any clue why it is so?

    The relationships between dimensions and fact tables are usually created before Measure Group creation, they are created in the Data source view.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by ScriptSoft Thursday, October 17, 2019 3:14 AM
    Wednesday, October 16, 2019 8:47 AM
  • Thanks Will for your reply

    • Edited by ScriptSoft Thursday, October 17, 2019 3:14 AM
    Thursday, October 17, 2019 3:14 AM