Focusbi: MDX Multi-Dimensional Model Retrieval

Public Account:Focusbi
Learn more about business intelligence, data warehouse, database development, crawler and Shanghai-Shenzhen Stock Market Data push. For questions or suggestions, please follow the public account to send a message;
If you think focusbi is helpful to you, you are welcome to forward your friends or click like at the end of the article [1].

Business Intelligence tutorial PDF

Link: https://pan.baidu.com/s/1f9VdZUXztwylkOdFLbcmWw password: 2r4v

Mdx Engine

Mdx is a structured query language that is more complex than the SQL query language, because MDX is used to search data for multidimensional data models to support data verification in complex business environments. Mdx supports two different modes: Expression Language: defining and Operating Analysis Service objects and data to calculate values. 2. Query Language: Retrieves data from Analysis Service.

A multi-dimensional dataset is an object designed and used for data analysis. In the data warehouse design stage, the dimension and fact data have been divided and processed. The SSAS engine is assembled into multidimensional data. Although each fact value can analyze the data according to the pre-designed dimension, this is easily achieved through SQL statements. Based on the characteristics of the multidimensional model, it is used to support data verification in complex business environments, therefore, business scenarios proposed by business personnel and management personnel should be easily implemented. Here, we will introduce a simple case study of the most common year-on-year comparison.

Year-on-year comparison

In this case, I use the real estate data to compare the contract amount with the contract amount;

Year-on-year = (current period/period)-1) * 100%; period-over-period = (current period/previous period)-1) * 100%; by analyzing this calculation formula, the existing values exist in the contracted value group. The key is how to get the same period and the previous period. The MDX engine provides a method function for getting the two data, however, it is necessary to use the date dimension. The idea of the same period is to take the previous year's today, and the idea of the previous period is to take the previous month's today. Therefore, the previous period can use the date dimension currentmember. prevmember, which can be obtained through the date dimension parallelperiod. obtained by currentmember

Steps

The Mode Supported by MDX is used here: Expression Language to define and operate the calculated value.

Step 1: open a project and change the date attribute to the time type );

 

Step 2: Open the multi-dimensional model, click the calculation button, click Create computing member, and change the name to [test_contract amount previous], expression: ([measures]. [contract amount], [a date]. [year-season-month-day]. currentmember. prevmember), display the folder to fill in the test.

 

Step 3: Click the compute button and click Create computing member. The name is changed to [test _ contract amount comparison], and the expression is format ([measures]. [contract amount]/([measures]. [contract amount], [a date]. [year-season-month-day]. currentmember. prevmember)-1, "0.00%"), display folder fill in test.

 

Step 4: Click the calculation button and click Create computing member. The name is changed to [test _ contract amount for the same period], expression :( parallelperiod ([a date]. [year-season-month-day]. [year], 1, [a date]. [year-season-month-day]. currentmember), [measures]. [contract amount]). Enter the test in the displayed folder.

 

Step 5: Click the compute button and click Create computing member. The name is changed to [test _ contract amount year-on-year comparison], and the expression is format ([measures]. [contract amount]/(parallelperiod ([a date]. [year-season-month-day]. [year], 1, [a date]. [year-season-month-day]. currentmember), [measures]. [contract amount])-1, "0.00%"), display folder fill in test.

 

Step 6: deploy multi-dimensional models. Here we will not introduce how to deploy these models in the previous article.

Step 7: Click the browser to drag the date dimension hierarchy year-month-day from the model to select the dimension area and select the first four days of the month, February 2016 and February 2017, drag year-season-month-day to the value area. Drag the contract amount from the contract value group to the value area, test Value group: Drag test _ contract amount to the previous period, test _ contract amount to the previous period, test _ contract amount to the same period, test _ contract amount to the same period, and test _ contract amount to the same period, click query.

 

 

Step 8: based on the year-on-year comparison and period-over-period comparison formula, we can see that in the first quarter of, the signing amount of testing in the second quarter of = 1612800in The first quarter of _ the signing amount of the signing amount in the third quarter period = 16128001; both of them indicate that the data in the previous period and the same period is correct. In this case, we can remove the date from the date dimension to verify whether the data is correct?

 

Mdx function dictionary

Mdx is much more difficult than SQL. It involves many spatial concepts. At half past one, it will be difficult for many people to understand and learn, because there are very few official learning materials and books about MDX; when I reverse engineer the multidimensional data model, I got a very comprehensive MDX language function table using a total of 404 functions, this table has been embedded in the Bi project management system. I will import it to share it with you. Follow the focusbi command to send the MDX function to get the download link. You can simply send the function directly. Part of this MDX function table is in full English. If you have the ability to translate the description column, please translate it and share it with me.

 

 

 

Focusbi: MDX Multi-Dimensional Model Retrieval