5.2 define fact relationships

Define fact relationships :

You sometimes need to define the dimension of the measurement value based on the data items in the fact data table, or query other specific information in the fact data table, such as the invoice number related to the specific sales situation or the purchase order number. When a dimension is defined based on such fact data table items, the dimension is called a "fact dimension ".". A fact dimension is also called a degradation dimension. To combine relevant fact data table rows (for example, all rows related to a specific invoice number), The fact dimension is very useful. Although this information can be placed in a single dimension table in a relational database, creating a separate dimension table for this information does not have any benefit because the dimension table and fact table grow at the same speed, only duplicate data is created and unnecessary complexity is increased.

In Microsoft SQL Server 2005 Analysis Services (SSAs), you can determine whether to copy the fact Dimension Data in the molap dimension structure to improve query performance, or whether to define the fact dimension as a ROLAP dimension, to save storage space at the cost of query performance. When dimensions are stored in the molap storage mode, all dimension members are stored in the analysis services instance of the highly compressed molap structure in addition to storing dimension members in the partition of the metric value group. When dimensions are stored in the ROLAP storage mode, only dimension definitions are stored in the molap structure, while dimension members themselves are queried from basic relational fact data tables during query. The appropriate storage mode can be determined based on the query frequency in the fact dimension, the number of rows returned by a typical query, the query performance, and the processing cost. When a dimension is defined as a ROLAP, it is not required that all cubes of the dimension be stored in the ROLAP storage mode. This is different from SQL Server 2000 Analysis Services.

When defining a fact dimension, you can define the relationship between a fact dimension and a measurement value group as a fact relationship. The following constraints apply to fact relationships:

• The granularity attribute must be a key column of a dimension. This key column creates a one-to-one relationship between the dimension and facts in the fact table.

• A dimension can have only one fact relationship with a single measurement value group.

After each update of the measurement value group referenced by the fact relationship, the fact dimension must be incrementally updated.

 

For more information, see And .

In the tasks of this topicCustomerponumberColumn inFactinternetsalesAdd a new dimension to a fact data table. Then add the dimension and"Internet sales"The relationship between measurement value groups is defined as a fact relationship.

Define Internet sales order fact dimension
Define Internet sales order fact dimension

2. In the multidimensional cube designer of the Analysis Services tutorial cube, click"Multidimensional Dataset structure"Tab.

4. Right-click"Dimension"Any position in the pane, and then click"Add cube dimension".

6. In"Add cube dimension"In the dialog box, click"Create dimension".

   The dimension wizard is displayed.

8. In"Welcome to the dimension wizard"Page, click"Next".

10. In"Select data source view"Page, click"Next"Select data for this dimension from the adventure works DW Data Source view.

12. In"Select dimension type"Page, click"Next"Specify a standard dimension.

14. In"Select primary dimension table"Page, select"Primary table"In the listDBO. factinternetsales.

    Note: A key combination has been defined as a key Column Based on the salesordernumber and salesorderlinenumber columns, and the salesorderlinenumber column is defined for the member name attribute. Note that you cannot define columns for the member name attribute from any table other than the factinternetsales table.

16. Click"Next".

18. In"Select related table"Page, make sure that no table is selected, and then click"Next".

20. In"Select dimension attributes"Page, clear all selected dimension attributes, and then select"Customer Po no"Check box for dimension attributes.

    Note: you cannot select any combination of keys in the dimension wizard as attributes. This is because these key combinations have been defined as column keys. To add these keys as attributes based on their own conditions, you must define them as attributes after defining dimensions.

22. Click"Done"To change the dimension name"Internet sales order details", Click"Done"And then click"OK"To add the newly created database dimension to the Analysis Services tutorial Cube as the cube dimension.

24. Open"Internet sales order details"Dimension designer.

26. In"Features"In the pane, select"Internet sales"In the "properties" windowNameProperty changedDescription".

28. InNamecolumnIn the attribute unit, select"(New )", SetProductSelect the source table in"Object binding"In the dialog boxEnglishproductnameSelect the source column, and then click"OK".

30. Set"Data Source view"In the paneInternetsalesTableSalesordernumberDrag column"Features"Pane"Sales order number"Add a property to a dimension.

32. The new"Sales order number"FeatureNameProperty changed"Order No", SetOrderbyProperty changedKey.

34. In"Hierarchy and level"In the pane, create"Internet sales order"User hierarchy, which is included in the order"Order No"AndDescription"Level.

36. In"Features"In the pane, select"Internet sales order details"And view the "properties" window.StoragemodeAttribute Value.

    Note: this dimension is stored as a molap dimension by default. Changing the storage mode to ROLAP can save processing time and storage space, but this will reduce query performance. To achieve the purpose of this tutorial, you will use molap as the storage mode.

define the fact relationship of a fact dimension
define the fact relationship of a fact dimension

1. switch to the multidimensional cube designer of the Analysis Services tutorial cube, and click the dimension usage tab.

   Note: "Internet sales order details" the dimension of A Multidimensional Dataset is automatically configured with a fact relationship, as shown in the unique icon.

   the "Internet sales order details" Multidimensional Dataset is displayed.

2. at the intersection of "Internet sales" measurement group and "Internet sales order details" , click the ellipsis (... ) to view the fact link attributes.

   the define link dialog box is displayed. Note that you cannot configure any attribute.

   the fact link attribute in the define link dialog box is displayed.

3. click cancel .

Use fact dimension to browse Multi-Dimensional Datasets
Use fact dimension to browse Multi-Dimensional Datasets

2. In"Generate"In the menu, click"Deployment Analysis Services tutorial"To deploy the changes to the Analysis Services instance and process the database.

4. After the deployment is complete, click"Browser"Tab, and then click"Reconnect".

6. Clear"Data"All measurement values and hierarchies in the pane"Internet sales-sales"Add metric values"Data"The data area of the pane.

8. In"Metadata"In the pane, expand"Customer","Location","Customer region","Member","All customers",Australia,Queensland,Brisbane,4000, Right-clickAdam PowellAnd then click"Add to subcube area".

   By filtering the limit on sales orders that are returned to a single customer, you can gain a deeper understanding of the basic details in large fact data tables without significantly reducing query performance.

10. Set"Internet sales order details"Dimension"Internet sales order"Add User-defined hierarchies"Data"The row area of the pane.

    Note that Adam Powell's sales order number and corresponding Internet sales volume will appear in"Data"Pane.

12. Expand each sales order number in the line area to view the details of each line item in these orders.

    Shows the process of determining the dimension in the previous step.