4.7 define unknown members and null value processing attributes

Define unknown members and null value processing attributes :

When Microsoft SQL Server 2005 Analysis Services (SSAs) processes a dimension, all non-repeated values in the basic columns of the table or view in the data source view are filled with the attributes in the dimension. If Analysis Services encounters a null value during processing, by default, it converts the null value to zero in the numeric column or to an empty string in the string column, therefore, no error is thrown. You can modify these default settings or convert null values in the basic Relational Data Warehouse extraction, conversion, and loading processes (if any. You can also configure the following three attributes to convert the null value of Analysis Service to the specified value:UnknownmemberAndUnknownmembernameAttributes andNullprocessingAttribute.

The dimension wizard and cube wizard correctly enable these attributes based on whether the dimension key feature can be empty or whether the root feature of the snowflake dimension is based on the columns that can be empty. In these casesNullprocessingProperty will be setUnknownmember, AndUnknownmemberProperty will be setVisible.

The default value of unknown members is unknown. You can setUnknownmembernameAttribute Value to specify a different value.

 

However, when a snowflake dimension is generated incrementally (as we are dealing with the product dimension in this tutorial, alternatively, when you use the dimension designer to define a dimension and then merge these existing dimensions into a multi-dimensional dataset, you may need to manually setUnknownmemberAndNullprocessingAttribute.

In the following task, you will see that when Analysis Services processes a snowflake dimension, analysis services deletes its value as a feature member that is null for the column linked to the snowflake table, unless you modify the settings of a specific attribute. By default, analysis services ignores such errors, so no errors will occur;NullkeycovertedtounknownThe default attribute isIgnoreerror. Analysis Services deletes attribute members that contain null values because it processes internal connections between two snowflake tables.

You can perform the following steps to control how Analysis Services performs processing in this case:

• Enable dimensionUnknownmemberAttribute.

• DimensionUnknownmembernameAttribute specifies a value.
• Set the relationship between attributes and dimension property links.
• Defines custom error handling for key columns that link snowflake tables.

In the tasks of this topic, the product category and product subcategory attributes are added to the Product Dimension of the snowflake table. These snowflake tables are added to the adventure works DW Data Source view. Then, enableUnknownmemberProperty; Set"ProgramSet components"SpecifiedUnknownmembernameAttribute Value."Subcategory"And"Category"The relationship between the property and the product name property. Finally, custom error handling is defined for the member Key Attribute Linked to the snowflake table.

If the "sub-category" and "category" attributes are added to the Analysis Services tutorial multi-dimensional data set by using the cube wizard, these steps are automatically performed.

 

View error handling and unknown member attributes in the Product Dimension
View error handling and unknown member attributes in the Product Dimension

2. SwitchProductDimension designer, click"Dimension Structure"Tab, and then"Attribute"Select"Product".

   You can view and modify attributes of a dimension.

4. In the "properties" window, ViewUnknownmemberAndUnknownmembernameAttribute.

   Note,UnknownmemberAttribute is not enabled because the attribute value is setNoneInsteadVisibleOrHiddenAnd is notUnknownmembernameAttribute name.

6. In the Properties windowErrorconfigurationIn the Attribute cell, select"(Custom )", And then expandErrorconfigurationAttribute Set.

   SetErrorconfigurationSet property"(Custom )"Allows you to view default error configuration settings, but does not change any settings.

8. Check the key and empty key for incorrect configuration properties, but do not make any changes.

   Note: by default, if a null key is converted to an unknown member, the processing error associated with the conversion is ignored.

   ShownErrorconfigurationSet the attributes of an attribute set.

10. Click"Browser"Tab to verify that"Hierarchy"Selected in the list"Product model series", And then expand"All products".

    Pay attention to the five members of the product series.

12. Expand"Component", And then expand"Model name"Level of unmarked members.

    This level includes the component components used to generate other components (fromAdjustable raceAs shown in.

Define attributes from the snowflake table and the "product category" user-defined hierarchy
Define attributes from the snowflake table and the "product category" user-defined hierarchy

2. Open the data source view designer for the data source view of adventure works DW"Graph organization program"Select"Distributor sales"In business intelligence development studio"Data Source view"Click"Add/Delete tables".

   The"Add/Delete tables"Dialog box.

4. In"Contained object"List, selectDBO. dimproductAnd then click"Add related table".

   DBO. dimproductsubcategoryThe table is added"Contained object"List.

6. InDBO. dimproductsubcategoryWhen the table is selected as the latest table by default, click"Add related table".

   "DBO. dimproductcategory"The table is added"Contained object"List.

8. Click"OK".

10. In bi development studio"Format"Menu, pointing"Automatic Layout"And then click"Relationship Diagram".

    Note,DBO. dimproductsubcategoryTable andDBO. dimproductcategoryTables are linked to each other andProductTable LinkResellersalesTable, as shown in.

12. SwitchProductDimension designer, and then click"Dimension Structure"Tab.

14. Right-click"Data Source view"Any position in the pane, and then click"Show All Tables".

16. In"Data Source view"In the pane, findDimproductcategoryTable, right-clickProductcategorykeyAnd then click"Create attribute from column".

18. In"Attribute"In the pane, change the name of this new property"Category".

20. In"Attribute"Window, clickNamecolumnAttribute cell, select"(New )"And then"Object binding"Dialog Box"Source table"Fields and"Source column"SpecifyDimproductcategoryAndEnglishproductcategorynameAnd then click"OK".

22. In"Data Source view"In the pane, findDimproductsubcategoryTable, right-clickProductsubcategorykeyAnd then click"Create attribute from column".

24. In"Attribute"In the pane, change the name of this new property"Subcategory".

26. In"Attribute"Window, clickNamecolumnAttribute cell, select"(New )", And then"Object binding"Dialog Box"Source table"FieldDimproductsubcategory, In"Source column"FieldEnglishproductsubcategorynameAnd then click"OK".

28. Create"Product category"The new user-defined hierarchy, which has the following levels from top to bottom:"Category","Subcategory"And"Product name".

30. Add "all products"Is specified as the" product category "User-Defined LevelAllmembernameAttribute Value.

Browse user-defined hierarchies in the Product Dimension
Browse user-defined hierarchies in the Product Dimension

2. InProductDimension"Dimension designer"Of"Dimension Structure"On the tab toolbar, click"Processing".

4. Click"Yes"To generate and deploy the project, and then click"Run"To processProductDimension.

6. After successful processing"Processing progress"Expand in the dialog box"Processing dimension 'product' completed successfully", Expand"Processing dimension attribute 'product name' has been completed successfully", And then expand"SQL query 1".

8. Click Select distinct query, and then click"View Details".

   Note that the WHERE clause has been added to the select distinct clause. This removes products that do not contain values in the productsubcategorykey column, as shown in.

10. Click"Close"Three times, close all processing dialog box.

12. ClickProductIn the dimension designer of a dimension"Browser"Tab, and then click"Reconnect".

14. Verify"Product model series"Whether it appears in"Hierarchy"List, expand"All products", And then expand"Component".

    Note that the list of all part components is not listed because the WHERE clause is used in the select distinct statement, as shown in.

16. Select"Hierarchy"In the list"Product category", Expand"All products", And then expand"Component".

    Note that no widget is displayed.

To modify the behavior mentioned in the previous Task, follow these steps:UnknownmemberAttribute, SetUnknownmembernameAttribute Value, Set"Subcategory"And"Model name"FeatureNullprocessingSet propertyUnknownmember, Set"Category"Feature is defined"Subcategory"Feature-related features"Product series"Feature is defined"Model name"Features. The preceding steps enable analysis servicesSubcategorykeyEach product that does not contain values in the key column uses an unknown member name value, as shown in the following task.

Enable unknown members, define property relationships, and specify custom processing properties for null values
Enable unknown members, define property relationships, and specify custom processing properties for null values

2. ClickProductIn the dimension designer of a dimension"Dimension Structure"Tab.

4. In"Attribute"Window, SetProductDimensionUnknownmemberProperty changedVisibleUnknownmembernameProperty value changed"Assembly components".

   SetUnknownmemberProperty changedVisibleOrHiddenTheUnknownmemberAttribute.

6. In"Attribute"In the pane, expand the following attributes:

   • Product Name

   • Sub-category
   • Model name

   Note,"Product series"And"Model name"And indirectly linked"Product name"Key attribute. Note that"Subcategory"Property defines the property relationship,"Category"The property is directly linked"Product name"Attribute.

8. Set"Category"Attribute Link"Product name"Drag property"Subcategory"Attribute.

   Now,"Category"Attribute passed"Subcategory"The attribute is linked to the row in the fact data table, and the "sub-category" attribute is passed"Product name"Link the attribute to the row in the fact data table.

10. In"Attribute"In the pane, select"Subcategory"In the Properties windowKeycolumnsClick the ellipsis (...).

12. In"Dataitem Set Editor"In the dialog box, SetNullprocessingProperty changedUnknownmember, As shown in.

14. Click"OK".

16. In"Attribute"In the pane, select"Model name"In the Properties windowKeycolumnsClick the ellipsis (...).

18. In"Dataitem Set Editor"In the dialog box, SetNullprocessingProperty changedUnknownmemberAnd then click"OK".

    Due to the above changes, when Analysis Services encounters a null value for the "sub-category" attribute or "model name" attribute during processing, it replaces it with an unknown member value as a key value, the user-defined hierarchy is constructed correctly.

View the Product Dimension again
Browse Product Dimension

2. In"Generate"Click"Deployment Analysis Services tutorial".

4. After the deployment is completed, clickProductIn the dimension designer of a dimension"Browser"Tab, and then click"Reconnect".

6. Verify"Hierarchy"Selected in the list"Product category", And then expand"All products".

   Note: The Assembly component is displayed as a new member of the category level.

8. Expand"Category"Level"Assembly components"Member, and then expand"Subcategory"Level"Assembly components"Member.

   Note that"Product name"All assembly components are displayed, as shown in.

10. In"Hierarchy"Select from the list"Product model series", Expand"All products","Product series"Level"Assembly components"Members and"Model name"Level"Assembly components"Member.

    Note that"Product name"All assembly components are displayed.

Download and install the updated example to obtain the complete project used in Lesson 4th. For more information, see To obtain the updated example ".