SSAS & Excel BI Tips Article Two: Performance tuning KeyColumn & NameColumn

The SSAs dimension attribute (Attribute) has the following three properties: KeyColumn, NameColumn, ValueColumn, as shown in the date dimension of the AW setting for these three column properties, And we use the wizard to generate the dimension by default only KeyColumn settings, if not manually set NameColumn, its value is equal to KeyColumn, if not manually set ValueColumn, its value is the default equals NameColumn. In general, there is no problem with using the wizard to generate the SSAS dimension when the data volume of the dimension table is small, and if the dimension table has a slightly larger amount of data, such as the dimension table on TENS, it will have to be optimized.

First we have to understand these three column properties, open an MDX query in SSMs, the data source is the AW cube, expand the Date dimension, navigate to a date member [January 1, 2005], and drag and drop to the right of the Script editing window, you can see the generated script is [ Date]. [date].&[20050101], the data source represents the member name [January 1, 2005] is NameColumn, dragged out the generated script displays the member name [20050101 ] is keycolumn.

We'll execute the following script and see [Date]. [date].&[20050101] This member's three column property corresponds to the value

with MEMBER Measures.valuecolumn as [Date]. [date].&[20050101]. Membervalue

MEMBER Measures.keycolumn as [Date]. [date].&[20050101]. Member_key

MEMBER Measures.namecolumn as [Date]. [date].&[20050101]. member_name

SELECT {measures.keycolumn, measures.namecolumn, Measures.valuecolumn} on 0

from [Adventure Works]

Then compare the settings in the property edit box to understand

We see that keycolumn is an int integer that uniquely identifies the member of the dimension attribute; NameColumn is a WCHAR string that is used to present the friendly name to the user on the client; ValueColumn is the date type. Typically used to store additional information for use when creating calculation, or for intelligent analysis by clients, such as when setting ValueColumn as a date date type, an Excel client in the right-click menu will have a date filters. Smart Filter Menu

Assuming that we have one of the following member dimension tables and follow the wizard to generate the default SSAS dimension, the three column of the attribute for membership level (DIMACCOUNT.TIER_VARCHAR) is set by default, KeyColumn is a string type, NameColumn and ValueColumn are not set, default equals KeyColumn

And then look at what you need to do with the database to work with this dimension, copy the script shown to SSMs, and see if you want to re-query the membership level field of the Member dimension table.

Next we make some changes to the Member dimension table, change the data structure to show the effect, the membership level no longer use the varchar type, instead of using tinyint, and add a dictionary table, record each member level number corresponding to the rank name

If the primary foreign key relationship for both tables is not set in the database, you can specify the relationship in the SSAS data source view as follows

When you create a DimAccount2 dimension, the wizard prompts you for a related table Dimtier, and the default tick does not change

When you make a selection of available properties, leave the default to only the tier ID not changed, remember not to select tier name, mainly to use this tinyint type of tier ID, you can rename it to tier name

The three column settings for the DimAccount2 dimension tier attribute generated for the wizard, see KeyColumn as the tinyint type, NameColumn and valuecolumn default to NULL, if the dimension is being deployed at this time, Then the client will show the tier is a number, not readable, so here we want to set the next NameColumn, specify the name listed as Tier_name, so as to ensure that the client can show the readability of the tier

Let's look at what we need to do with the database to handle the DimAccount2 dimension and copy the script that handles tier Name (which is actually tier ID).

Can see different from DimAccount1 is, here is just dimtier this dictionary table tier_id, tier_name to go to re-query, relative to the membership table, this dictionary table is really small cute

Go to SSMs to verify, drag the gold member into the script edit box and see that the [Tier name].&[2] is displayed, stating that the SSAS Dimension data store is indeed a tinyint type, It is not mapping to the corresponding name string until it needs to be displayed by the client, and this mapping is retrieved from the Tier dictionary table to name.

Again with the member verification

We understand the principle of optimization, first of all, to deal with (process full) These two Member dimension table, it is necessary to go to the RDBMS data source to do the whole table read, assuming that the membership table has 10 million rows of records, and similar to the membership level such as the field has 10, then a rough calculation, The first member table designed as a string requires 800M of disk space (no table compression), and the second tinyint-designed membership table takes up only 100M of space, which means that SSAS goes to the database to perform the full table query for the two membership tables, To go to disk to read 800M and 100M of data respectively, this disk overhead is 8 times times the difference, then the data loaded into the buffer pool memory buffers, and will produce several times the difference in memory cost, and then the data processing to the SSAS Cube Dimension (also disk files), again generate unequal disk overhead, in addition to distinct to recalculate, the former need to the membership dimension of this large table to do tier to heavy, the latter only need to the membership level this small dictionary table to do the heavy, obviously the calculation is different weight class, These are just the cost of the process, perhaps you are doing in the night, not care these, then the performance of the query is always concerned about it, Dimension size will also affect the query performance.

To add, avoid abusing this optimization scheme, variable string type to a smaller number type is to save space, thereby reducing disk and memory overhead, only in the high repetition of the field is meaningful, if the field itself is very low repetition, it would be counterproductive.

Finally, see how the SSAS Performance Guide white paper describes this benefits:

• Eliminate the need for extraneous attributes. Reducing the total number of attributes used in your design also makes it more efficient.
• Reduce processing time, reduce the size of the dimension.

SSAS & Excel BI Tips Article Two: Performance tuning KeyColumn & NameColumn