Parameter linkage solution Environment Data Warehouse + OLAP (cube) + Reporting Service [original]

The topic of today's blog is: parameter linkage solution.

Environment: Data Warehouse + OLAP (cube) + Reporting Service. Effect: multiple parameters (1, 2 ,......) Achieve interaction.

First, let's talk about the principle. A data warehouse consists of fact tables and dimensions. I understand that fact tables are actually a table composed of multiple dimensions related to a topic and the values of these dimensions. A dimension is a dictionary. When you read a record from a fact table, you need to look at the corresponding solution and sentence from different perspectives in the dictionary. These are attributes. Any meaningful attributes related to a dimension can be stored in it (including those foreign keys ).

How can we cascade parameters? Of course, there is a relationship between them. After you select the Department parameters, you only want the item parameters to show the parameter sets related to them. This is the relationship. From SQL, It is the Screening Conditions for nested select statements and in conditions after where.

Solution 1: The parameter comes from the cascade of the same dimension. By reading the following blog, the author has clearly explained the entire construction process.

Http://www.cnblogs.com/PumpkinDatabase/archive/2009/09/29/1576151.html

From the MDX, we can see that the parameter set corresponding to each parameter is the from {@ parameter} on columns, which indicates the significance of where in SQL, it's just a filtering condition. Well, let's expand the extended thinking. If another parameter is added and the parameter comes from another dimension, how can this problem be solved?

Solution 2: Establish a property relationship. Principle: Link! Since you use three fields in one dimension as three parameter cascade, you can also use another dimension as the foreign key to redundant one field, become a member of the three parameters.

The specific implementation method is: Modify the dimension table structure corresponding to the preceding material parameters, and add the Department dimension key as the foreign key and a dimension Association. add the table relationships and "attribute relationships" of materials and departments to the cube ". Figure:

In this case, the fact table is associated with the material, and the material is associated with the Department. When building a data set in reporting service, expand the material dimension. In the same solution, drag the materials and departments in the material dimension to the filtering bar as the filtering condition and select as a parameter.

Do you find any problems? First, it is annoying to change the table structure. We need to rethink the table structure and extract data. Second, the parameters are all from the dimension. When a parameter value is selected, no data is displayed in the report. Third, when it needs to associate other dimensions for parameters, and there is no inevitable relationship between dimensions, what should we do?

The third solution (ultimate, patience, I can't write it, but I'm excited to tell you ). Principle: link! But this time, let's look a little farther. The parameters you need will eventually settle in the fact table. In fact tables, they also establish various relationships through measurements. Well, with the above foundation. We do this:

1. In the Analysis Services Project, create a new fact dimension named parameter dimension. In "Data Source view", add a fact table and the dimension to be associated.

2. In the data source view, add the dimension fields that need to be set as parameters in the fact table to the attributes of the dimension. For example, select the newly added item (item) attribute Member, find the keycolumns in its attribute, check whether it corresponds to the itemkey of the fact table, and namecolumns corresponds to the itemname in dimitem. Other property members copy this method.

3. If the parameter needs to be displayed with a hierarchical structure, a parentkey is redundant when the attribute member is added, and its namecolumns is also modified.

4. Add the dimension to the cube. Deployment.

Error-prone: When this dimension is generated, I repeatedly stressed that keycolumns should correspond to the key of the fact table. If the key of the dimension is added, when it is added to the Cube, the same dimension is added again.

The result is as follows: the above three parameters are cascade with each other :), and no data is displayed.

 

Thank you very much for your patience in guiding me ~ Although I have never met you, thank you for your sincere treatment :)