Introduction to Business Intelligence Add dimension Intelligence

I think the knowledge involved in this article is not commonly used, can be used as a reference to understand.

One, account intelligence

As mentioned in the previous articles, set a custom dimension, which is an account-like dimension, SSAS dimension customrollupcolumn Usage-Custom aggregation method, this way can realize revenue = Total revenue-A custom aggregation method, MSBI also provides a business intelligence that is specific to the dimension of the account type, we only need to set the table of this account type, first build a normal account dimension, and then add business intelligence on this basis: right-click a normal Account dimension (type Regular), choose Add Business Intelligence, select ' Define account intelligence '.

Next, map the fields in your dimension table to the built-in account type, then, next, you can build a full account type dimension, and finally, when you browse the data, A smart dimension similar to the SSAS dimension customrollupcolumn Usage-Custom aggregation method is implemented.

Second, time intelligence

First of all, you want to prepare a dimension of a time type (the type is times), and then right-click the cube to add Business intelligence:

Then, Next: Select the level at which you want to add the time calculation, click Next, SSAS will let you choose, with the time calculation associated with the measure, here is not given, I chose the year-on-year growth rate and growth;

Finally, when the addition is complete, the change is that there is one more hierarchy in the dimension:

In addition, you can see in the Calculation tab of cube that one more calculated member is actually the MDX script calculation of the measure associated with the time calculation:

The following MDX script statement for this calculated member:

/*the CALCULATE command controls the aggregation of leaf cells in a multidimensional dataset. If you delete or modify it, the data in the cube is affected. This command should only be edited when you want to manually specify how the cube is aggregated. */CALCULATE;/*start [Dim Date]. The time intelligence script for the hierarchy hierarchy. */Create Member Currentcube. [Dim Date]. [Hierarchy Dim Date calculation].   [Annual year-on-year growth rate] as"NA" ; Create Member Currentcube. [Dim Date]. [Hierarchy Dim Date calculation].   [Annual YoY growth] as"NA" ; Scope ({[measures].[ Total Amount-Fact IP Medicine]}) ;/*2011 Annual Growth Rate*/([Dim date].[ Hierarchy Dim Date calculation]. [Annual year-on-year growth rate], [Dim Date]. [Year-level]. [year-level]. Members (1), [Dim Date]. [Date Key]. Members)=NULL; /*2011 Annual Growth*/([Dim date].[ Hierarchy Dim Date calculation]. [Annual yoy growth], [Dim Date]. [Year-level]. [year-level]. Members (1), [Dim Date]. [Date Key]. Members)=NULL; /*Annual year-on-year growth rate*/([Dim date].[ Hierarchy Dim Date calculation]. [Annual year-on-year growth rate], [Dim Date]. [Year-level]. [year-level]. Members (1): Null, [Dim Date]. [Date Key]. Members)=(([Dim date].[ Hierarchy Dim Date calculation]. [Current Dim Date], [Dim date]. [Hierarchical structure]. CurrentMember)-([Dim date].[ Hierarchy Dim Date calculation]. [Current Dim Date], ParallelPeriod ([Dim date].[ Hierarchical structure]. [Year-level],1, [Dim Date]. [Hierarchical structure]. CurrentMember )))/([Dim date].[ Hierarchy Dim Date calculation]. [Current Dim Date], ParallelPeriod ([Dim date].[ Hierarchical structure]. [Year-level],1, [Dim Date]. [Hierarchical structure].    CurrentMember)); ([Dim Date]. [Hierarchy Dim Date calculation]. [Annual year-on-year growth rate], [Dim Date]. [Year-level]. [year-level]. Members (0), [Dim Date]. [Date Key]. Members)=Null; Format_String (([Dim date].[ Hierarchy Dim Date calculation]. [Annual year-on-year growth rate], [Dim Date]. [Date Key]. Members))="Percent" ; /*Annual YoY Growth*/([Dim date].[ Hierarchy Dim Date calculation]. [Annual yoy growth], [Dim Date]. [Year-level]. [year-level]. Members (1): Null, [Dim Date]. [Date Key]. Members)=([Dim date].[ Hierarchy Dim Date calculation]. [Current Dim Date], [Dim date]. [Hierarchical structure]. CurrentMember)-([Dim date].[ Hierarchy Dim Date calculation]. [Current Dim Date], ParallelPeriod ([Dim date].[ Hierarchical structure]. [Year-level],1, [Dim Date]. [Hierarchical structure].    CurrentMember)); ([Dim Date]. [Hierarchy Dim Date calculation]. [Annual yoy growth], [Dim Date]. [Year-level]. [year-level]. Members (0), [Dim Date]. [Date Key]. Members)=Null; End Scope;/*ends [Dim Date]. The time intelligence script for the hierarchy hierarchy. */  

Then, deploy the project and browse the data: You can simply see that annual growth and growth are calculated automatically.

Three, server time dimension and dimension intelligence

Server time dimension, generally speaking, when you create a new dimension, you do not have a corresponding Time dimension table in your DW, you can take this approach instead of the time dimension, the steps are simple: Create a new dimension, select the server Time dimension, simply set the selection, and then the next step, the final completion is this:

Below we can deploy the processing project and then browse the data in the time dimension based on the server time: This method is sometimes desirable, this usage is specific to the use of the method, in the use of specific discussion. Not much to say here.

Dimension intelligence, in fact, is to add business intelligence, choose the dimension of intelligence, but also to customize the dimension to be added to meet their own needs to achieve intelligence, after all, built-in business intelligence use limited scope. When we add business intelligence, in addition to the above mentioned several intelligent dimensions, there are other intelligent dimensions, the general idea is the same, the knowledge of the specific implementation of the process is different; Ultimately, the goal is to achieve the so-called intelligence according to their own will.

Introduction to Business Intelligence Add dimension Intelligence