Defining the Unknown Member and Null Processing Properties

  • Article

When Microsoft SQL Server 2005 Analysis Services (SSAS) processes a dimension, all distinct values from the underlying columns in the tables or views in the data source view populate the attributes in the dimension. If Analysis Services encounters a null value during processing, by default, it converts this null to a zero for numeric columns or to an empty string for string columns - no error is thrown. You can modify these defaults or convert null values in your extract, transform, and load process (if any) of the underlying relational data warehouse. You can also have Analysis Service convert the null value to a designated value by configuring three properties: the UnknownMember and UnknownMemberName properties for the dimension and the NullProcessing property for the dimension's key attribute.

The Dimension Wizard and the Cube Wizard will properly enable these properties for you based on whether the key attribute of a dimension is nullable or the root attribute of a snowflake dimension is based on a nullable column. In these cases, the NullProcessing property of the key attribute will be set to UnknownMember and the UnknownMember property will be set to Visible.

Note

The default value for the unknown member is Unknown. You can specify a different value by setting a value for the UnknownMemberName property.

However, when you build snowflaked dimensions incrementally, as we are doing with the Product dimension in this tutorial, or when you define dimensions using Dimension Designer and then incorporate these existing dimensions into a cube, the UnknownMember and NullProcessing properties may need to be set manually.

As you will see in the following task, when Analysis Services processes a snowflake dimension, Analysis Services removes the attribute members whose value is null for the column that links the snowflaked tables, unless you modify the settings for certain properties. No error occurs because, by default, Analysis Services ignores this kind of error; by default, the NullKeyCovertedToUnknown property is set to IgnoreError. Analysis Services removes the attribute member with the null value because it performs an inner join between the two snowflaked tables.

You can control how Analysis Services performs processing in this situation by taking the following steps:

  • Enable the UnknownMember property for the dimension.
  • Specify a value for the UnknownMemberName property for the dimension.
  • Set attribute relationships that link dimension attributes appropriately.
  • Define custom error handling for the key column that links the snowflaked tables together.

In the tasks in this topic, you will add the product category and product subcategory attributes to the Product dimension from snowflaked tables that you will add to the Adventure Works DW data source view. You will then enable the UnknownMember property for the Product dimension, specify Assembly Components as the value for the UnknownMemberName property, relate the Subcategory and category attributes to the product name attribute, and then define custom error handling for the member key attribute that links the snowflaked tables.

Note

If you have added the Subcategory and Category attributes when you originally defined the Analysis Services Tutorial cube using the Cube Wizard, these steps would have been performed for automatically.

Reviewing Error Handling and Unknown Member Properties in the Product Dimension

To review error handling and unknown member properties in the Product dimension

  1. Switch to Dimension Designer for the Product dimension, click the Dimension Structure tab, and then select Product in the Attributes pane.

    This lets you view and modify the properties of the dimension itself.

  2. In the Properties window, review the UnknownMember and UnknownMemberName properties.

    Notice that the UnknownMember property is not enabled, because its value is set to None instead of Visible or Hidden, and that no name is specified for the UnknownMemberName property.

  3. In the Properties window, select (Custom) in the ErrorConfiguration property cell, and then expand the ErrorConfiguration properties collection.

    Setting the ErrorConfiguration property to (Custom) allows you to view the default error configuration settings - it does not change any settings.

  4. Review the key and null key error configuration properties, but do not make any changes.

    Notice that, by default, when null keys are converted to the unknown member and the processing error associated with this conversion is ignored.

    The following image shows the property settings for the ErrorConfiguration properties collection.

    ErrorConfiguration property collection

  5. Click the Browser tab, verify that Product Model Lines is selected in the Hierarchy list, and then expand All Products.

    Notice the five members of the Product Line level.

  6. Expand Components, and then expand the unlabeled member of the Model Name level.

    This level contains the assembly components that are used when building other components, starting with the Adjustable Race product, as shown in the following image.

    Assembly components used to build other components

Defining Attributes from Snowflaked Tables and a Product Category User-Defined Hierarchy

To define attributes from snowflaked tables and a Product Category user-defined hierarchy

  1. Open Data Source View Designer for the Adventure Works DW data source view, select Reseller Sales in the Diagram Organizer pane, and then click Add/Remove Tables on the Data Source View menu of Business Intelligence Development Studio.

    The Add/Remove Tables dialog box opens.

  2. In the Included objects list, select dbo.DimProduct, and then click Add Related Tables.

    The dbo.DimProductSubcategory table is added to the Included objects list.

  3. With the dbo.DimProductSubcategory table selected by default as the table most recently added, click Add Related Tables again.

    The dbo.DimProductCategory table is added to the Included objects list.

  4. Click OK.

  5. On the Format menu of BI Development Studio, point to Auto Layout, and then click Diagram.

    Notice that the dbo.DimProductSubcategory table and dbo.DimProductCategory table are linked to each other, and also to the ResellerSales table through the Product table, as shown in the following image.

    Diagram showing links between tables

  6. Switch to Dimension Designer for the Product dimension, and then click the Dimension Structure tab.

  7. Right-click anywhere in the Data Source View pane, and then click Show All Tables.

  8. In the Data Source View pane, locate the DimProductCategory table, right-click ProductCategoryKey in that table, and then click New Attribute from Column.

  9. In the Attributes pane, change the name of this new attribute to Category.

  10. In the Properties window, click the NameColumn property cell, select (new), and then specify DimProductCategory in the Source table field and EnglishProductCategoryName in the Source column field in the Object Binding dialog box, and then click OK.

  11. In the Data Source View pane, locate the DimProductSubcategory table, right-click ProductSubcategoryKey in that table, and then click New Attribute from Column.

  12. In the Attributes pane, change the name of this new attribute to Subcategory.

  13. In the Properties window, click the NameColumn property cell, select (new), and then specify DimProductSubcategory in the Source table field and EnglishProductSubcategoryName in the Source column field in the Object Binding dialog box, and then click OK.

  14. Create a new user-defined hierarchy called Product Categories with the following levels, in order from top to bottom: Category, Subcategory, and Product Name.

  15. Specify All Products as the value for the AllMemberName property of the Product Categories user-defined hierarchy.

Browsing the User-Defined Hierarchies in the Product Dimension

To browse the user-defined hierarchies in the Product dimension

  1. On the toolbar of the Dimension Structure tab of Dimension Designer for the Product dimension, click Process.

  2. Click Yes to build and deploy the project, and then click Run to process the Product dimension.

  3. When processing has succeeded, expand Processing Dimension 'Product' completed successfully in the Process Progress dialog box, expand Processing Dimension Attribute 'Product Name' completed successfully, and then expand SQL queries 1.

  4. Click the SELECT DISTINCT query and then click View Details.

    Notice that a WHERE clause has been added to the SELECT DISTINCT clause that removes those products that have no value in the ProductSubcategoryKey column, as shown in the following image.

    SELECT DISTINCT clause showing WHERE clause

  5. Click Close three times to close all processing dialog boxes.

  6. Click the Browser tab in Dimension Designer for the Product dimension, and then click Reconnect.

  7. Verify that Product Model Lines appears in the Hierarchy list, expand All Products, and then expand Components.

    Notice that all the list of assembly components are missing because of the WHERE clause in the SELECT DISTINCT statement, as shown in the following image.

    Hierarchy list showing missing components

  8. Select Product Categories in the Hierarchy list, expand All Products, and then expand Components.

    Notice that none of the assembly components appear.

To modify the behavior mentioned in the previous task, you will enable the UnknownMember property of the Products dimension, set a value for the UnknownMemberName property, set the NullProcessing property for the Subcategory and Model Name attributes to UnknownMember, define the Category attribute as a related attribute of the Subcategory attribute, and then define the Product Line attribute as a related attribute of the Model Name attribute. These steps will cause Analysis Services to use the unknown member name value for each product that does not have a value for the SubcategoryKey column, as you will see in the following task.

Enabling the Unknown Member, Defining Attribute Relationships, and Specifying Custom Processing Properties for Nulls

To enable the unknown member, define attribute relationships, and specify custom processing properties for nulls

  1. Click the Dimension Structure tab in Dimension Designer for the Product dimension.

  2. In the Properties window, change the UnknownMember property for the Product dimension to Visible, and then change the value for the UnknownMemberName property for this dimension to Assembly Components.

    Changing the UnknownMember property to either Visible or Hidden enables the UnknownMember property for the dimension.

  3. In the Attributes pane, expand the following attributes:

    • Product Name
    • Subcategory
    • Model Name

    Notice that Product Line is related to the Model Name attribute and is thus indirectly linked to the Product Name key attribute. Notice that no attribute relationships have been defined for the Subcategory attribute and that the Category attribute is linked to the Product Name attribute directly through the key attribute.

  4. Drag the Category attribute relationship from the Product Name attribute to the Subcategory attribute.

    The Category attribute is now linked to the rows in the fact table through the Subcategory attribute, which in turn is linked to the rows in the fact table through the Product Name attribute.

  5. In the Attributes pane, select Subcategory, and then click the ellipsis button (…) in the KeyColumns property cell in the Properties window.

  6. In the DataItem Collection Editor dialog box, change the NullProcessing property to UnknownMember, as shown in the following image.

    DataItem Collection Editor dialog box

  7. Click OK.

  8. In the Attributes pane, select Model Name, and then click the ellipsis button (…) in the KeyColumns property cell in the Properties window.

  9. In the DataItem Collection Editor dialog box, change the NullProcessing property to UnknownMember, and then click OK.

    Because of these changes, when Analysis Services encounters a null value for the Subcategory attribute or the Model Name attribute during processing, the unknown member value will be substituted as the key value, and the user-defined hierarchies will be constructed correctly.

Browsing the Product Dimension Again

To browse the Product dimension

  1. On the Build menu, click Deploy Analysis Services Tutorial.

  2. When deployment has successfully completed, click the Browser tab in Dimension Designer for the Product dimension, and then click Reconnect.

  3. Verify that Product Categories is selected in the Hierarchy list, and then expand All Products.

    Notice that Assembly Components appears as a new member of the Category level.

  4. Expand the Assembly Components member of the Category level and then expand the Assembly Components member of the Subcategory level.

    Notice that all the assembly components now appear at the Product Name level, as shown in the following image.

    Product Name level showing assembly components

  5. Select Product Model Lines in the Hierarchy list, expand All Products, expand the Assembly Components member of the Product Line level, and then expand the Assembly Components member of the Model Name level.

    Notice that all the assembly components now appear at the Product Name level.

Note

A completed project through Lesson 4 is available by downloading and installing the updated samples. For more information, see Obtaining Updated Samples in Installing Samples.

Next Lesson

Lesson 5: Defining Relationships Between Dimensions and Measure Groups