Special language in bi-mdx

Special language in bi-MDX (lower)

Author: Dai ziliang and Li Miao

In the previous article, we started with simple syntax elements for the mdx language and introduced the basic to advanced applications of MDX in an advanced way, as well as the significant differences between MDX and SQL. This article will continue with the advanced application topic of MDX and introduce more extensive ways of using MDX.

Use MDX in SSIsWhen processing ETL, we sometimes need to embed the MDX language in the ETL process. In this way, we can query the required data from a multi-dimensional database during data conversion, you can also modify or delete a multi-dimensional database. In traditional bi projects, a data warehouse is established by the OLTP system, a multi-dimensional database is established, and report presentation or data mining is performed at last. When a report is displayed, we use MDX to query multidimensional databases to obtain the results. How can I save the results of this ad hoc query and even process further data? SQL Server 2005 SSIS provides dedicated tools for this type of operations, not only for multi-dimensional database-based data queries, in addition, you can modify the structure of a multi-dimensional dataset and process the data in the multi-dimensional dataset. Next, we will introduce this type of operations in SSIS:

Convert the MDX query results to tables in SQL Server 2005 SSIS

1) establish a data source connection. After setting up the SSIS project, pull the ole db data source from the data flow component, select new at the connection establishment, and then select Microsoft ole db provider for analysis servers 9.0. In the server name field, type the server and its instance name, select the authentication method, and select the multi-dimensional database to be used in the selection of multi-dimensional databases. Here, select adventure works DW.

2) use the MDX statement. In data access mode, select the SQL command, type the MDX Statement (1) to be queried, and enter: Select {[date]. [calendar]. [calendar quarter]. & [2003] & [1], [date]. [calendar]. [calendar quarter]. & [2003] & [2], [date]. [calendar]. [calendar quarter]. & [2003] & [3], [date]. [calendar]. [calendar quarter]. & [2, 2003] & [4]} on columns, {[product]. [product categories]. [category]. & [1]. siblings} on rows from [sales Summary] Where ([measures]. [sales amount]) Figure 1: Enter the MDX statement to query

3) Click Preview to view the MDX query result table.

4) Next, we can use the data conversion transformation component to rename the columns in the query results, or use the conditional split transformation component to select sales of more than 100000 in the first quarter. Finally, we will import the selected results to the database table. In this way, we implement the process of "reading data from the Cube> converting and integrating> storing data to another data table. Figure 2 shows the complete SSIS Data Flow task to implement this process.

Figure 2: complete SSIS Data Flow task

Use MDX in SQL Server 2005 SSIS to modify the Cube Structure

1) Select the SQL task execution component. We pull the SQL Task component from the control flow Toolbox into the design area, double-click it, select new connection in the Connection Manager, and select Microsoft OLE DB provider for analysis servers 9.0 from the provider, in this way, we connect to the multi-dimensional database, and then we can write the MDX statement.

2) enter the MDX statement of the cube to be modified in the SQL status to update the cube. 3. Here we choose to directly enter: Alter cube [adventure works] update dimension [destination currency]. [destination currency], default_member = [destination currency]. [destination currency]. [US dollar]

Figure 3: Enter the MDX statement to modify the cube

3) after running, we can see that the structure of the cube has changed.

Use Analysis Services execute DDL task in SQL Server 2005 SSIS

One of the SQL Server 2005 SSIS components is the analysis services execute DDL task, which can be embedded in XML/a text. In XML/a text, MDX statements can be embedded to execute relevant functions. The content shown in Figure 4 briefly introduces how to use this component to process the adventure works DW Multidimensional Dataset:

Figure 4: processing of the adventure works DW multi-dimensional dataset

Using the preceding three components (ole db data source, SQL task execution, and analysis services execute DDL task), we learned how to apply MDX in SQL Server 2005 SSIs. Through the ETL process, transactions such as creating/modifying a multi-dimensional database, querying data from a multi-dimensional data set, and writing back to a relational database are completed, realizing the exchange between a relational database and a multi-dimensional database.

3. MDX script

In the previous introduction, we learned that multiple computing members can be created by compiling MDX statements to facilitate the real-time computing of multi-dimensional dataset data. Then, we will also use the MDX script to modify the multi-dimensional dataset. The MDX script usually consists of one or more MDX expressions or statements. Through these expressions or statements, we can use a series of computing results to fill the multi-dimensional dataset. Open the computing tab of the Analysis Services multi-dimensional Database Designer. We usually use the "form View" structure (5), and click "Script View" (6 ), the two interfaces are quite different.

Figure 5: Form View Structure

Figure 6: Script View

In the original design, the viewable form editor was changed to the script editor. Under the calculate command in the script window, we can write MDX expressions or statements to define the computing process of multi-dimensional datasets. In comparison, the form editor is more like a wizard, which provides us with a personalized structure interface for defining a certain type of data items (such as computing members and naming sets; the script editor uses a simple text input interface, allowing us to directly enter a wide range of MDX statements and functions to complete the definition and calculation of complex logic MDX.

Mdx script Classification

In analysis services, MDX scripts are divided into two types:

The default MDX script.When we create a multi-dimensional dataset, analysis services will create a default MDX script for us, which will define the computing process of our entire multi-dimensional dataset. Specifically, when we open the computing tab when creating a new multi-dimensional data set, a separate calculate statement will inevitably appear, this statement is a default MDX script created by Analysis Services for us. Generally, this calculate statement is located at the beginning of the MDX script, indicating that the entire cube should be calculated during the first transfer. Of course, the default MDX script also contains the script name for creating a nameset (corresponding statement: Create set), assigning values, and computing members (corresponding statement: Create member.

User-Defined MDX script.As the name suggests, after creating a multi-dimensional dataset, we can add a custom MDX script as needed. This not only extends the computing function of the multi-dimensional dataset, but also implements many complex business rules.

In the MDX script, we can use the MDX statement to manage the context, scope, and flow control operations of the MDX script, here we can also use statements for creating computing members and naming sets to write scripts.

Type	Statement	Description
Manage script Context	Calculate	Calculate a subcube and determine the order of solving dimensions contained in the subcube.
	Existing	Calculates the specified set in the current context.
	Freeze	Lock the unit value of the specified sub-cube to its current value.
Management scope	Scope	Limits the scope of the specified MDX statement to the specified subcube.
Condition Statement	If	Add the reference condition logic to the MDX script. If the condition is true, execute the statement.
	Case	Return a specific value from multiple comparisons based on the conditional logic.

Mdx Script Parameters

When writing SQL statements, we sometimes use parameters according to the design requirements. In the MDX script, we can also use parameterized statements, and the parameter expressions are also different, still marked. In MDX, the only difference is that parameter settings only support text or scalar values. To create referenced members, sets, or tuples, we need to use the MDX function for conversion, for example, the strtoset function (this function returns a set constructed by a specified string expression in the multi-dimensional expression format) or the strtomember function (this function returns a member of a specified string expression in the multi-dimensional expression format) and so on.

Summary

We introduced the special language MDX in BI through two columns. Similar to the functions of SQL, MDX is used to create/modify multi-dimensional data structures and query multi-dimensional data results. However, compared with SQL, MDX has more powerful functions: defines richer data elements, such as computing members, namespaces, and KPIs. We can use MDX in multiple ways, such: embed MDX In the ETL process, use the "computing" and "KPI" designer view in as to edit MDX, and directly create MDX scripts.