Bi notes-solutions for SSAs database Process

 

This article describes several solutions of the SSAS database, and briefly introduces the application scenarios of various solutions.

 

Environment conventions: SQL Server 2008

Example Library: Adventure works DW

 

Method 1: process directly in the project

This method is often used in the development and testing stages.

This method is usually the first full processing. If the SSAS library is still updated, a prompt is displayed for deployment first.

You can see that the processing level in the process interface that appears is database.

You can also click cube (or mining structure) to process them separately:

There are also many ways to process cubes:

Dimensions can also be processed separately:

Also, partitions can be processed separately (if there is a partition), but instead of on the solution interface, you have to open the cube design view and then click the partition:

The incremental processing option does not exist when the SSAS library is deployed. This option exists only after the SSAS library is processed once.

This method is the most basic method. After all, the SSAS library itself was born here. It is applicable to situations where development and testing phases and production and development are not isolated and security requirements are not high.

 

 

 

Method 2: Process in the SSIS package

The advantage of this method is that it is easy to define.

Directly create a project in bids and select an integrated service project under business intelligence:

There is a modular module dedicated to processing the SSAS library.

You can see that in each module, you can define what content is processed.

Generally, the dimension is processed first and then the cube is processed.

Similarly, you can design your own incremental solution here. If you have defined an incremental solution, the Bi solution that follows this solution will become available. Finally, you need to deploy the SSIS package to the integration service and set the job and schedule.

For details about how to deploy and release the SSIS package, refer to the following two links:

Http://msdn.microsoft.com/zh-cn/library/ms137592.aspx

Http://msdn.microsoft.com/zh-cn/library/ms365338.aspx

For more information about the cube incremental processing solution, refer to my article.Article: Bi notes --- incremental processing of Multi-Dimensional Datasets

This method should be regarded as the most standard method in Microsoft's product line. A wide range of applications.

 

 

Method 3:CodeMedium Process

Although the Code processing method is complex, it is still flexible. It is mainly implemented by calling the AMO object.

Here we will only briefly describe how to process the database-Level Logic. (By enumerating the sub-objects of server objects under amo, you can easily define the processing of each SSAs object)

Server _ Server = new server ();

_ Server. Connect ("localhost ");

 

Database _ DB = _ server. Databases ["Adventure works DW 2008"];

 

// Generate xmla command.

_ Server. capturexml = true;

// Process command.

_ DB. Process ();

_ Server. capturexml = false;

 

// Out put xmla.

Foreach (string item in _ server. capturelog)

{

Console. writeline (item );

}

 

Console. writeline ("processing ");

 

// Execute

Xmlaresultcollection _ result = _ server. executecapturelog (True, true );

 

Console. writeline ("process finished ");

 

// Out put result

Foreach (xmlaresult R in _ result)

{

Foreach (xmlamessage m in R. Messages)

{

Console. writeline (M. Description );

}

}

 

Console. writeline ("finished ");

Console. Readline ();

[Supplement]:AppendixPowershelL version of Process

 

[Void] [reflection. Assembly]: LoadFile ("C: \ Program Files \ Microsoft SQL Server \ 100 \ SDK \ assemblies \ microsoft. analysisservices. dll ")

$ Server = new-object Microsoft. analysisservices. Server

$ Server. Connect ("localhost ")
$ Db = $ server. Databases. getbyname ("Adventure works DW 2008r2 ")

$ Server. capturexml = $ true
$ Db. Process ()
$ Server. capturexml = $ false

Write-output "processing ..."

$ Result = $ server. executecapturelog ($ true, $ true)
Foreach ($ R in $ result)
{
Foreach ($ m in $ R. Messages)
{
Write-output $ M. Description
}
}

 

Write-outPut "done"

 

 

Pay attention to the _ server. the capturexml part. Because we want to process the database, the process method is called directly when we enumerate the database directly. The relevant xmla commands will be saved in the capturelog attribute of _ server. The first foreach of the Code is the part of the xmla command generated in reality.

You can also use ascmd to process the cube. For details about how to operate ascmd, refer to the previous article:

Bi notes --- several methods for deploying SSAs

Description of how to deploy the SSAS library in ascompu.exe.

The SSAS granularity that can be processed by code is very fine. Refer to the Add analysis services object in this article to see which objects can be enumerated, and use the following code, you can also enumerate these objects:

Server _ Server = new server ();

_ Server. Connect ("localhost ");

 

Foreach (Database _ dB in _ server. databases)

{

Console. writeline ("Database:" + _ dB. Name );

Foreach (cube _ cube in _ dB. cubes)

{

Console. writeline ("-- Cube:" + _ cube. Name );

Foreach (measuregroup _ Mg IN _ cube. measuregroups)

{

Console. writeline ("---- measure group:" + _ Mg. Name );

Foreach (partition _ Pt IN _ Mg. partitions)

{

Console. writeline ("------ partition:" + _ pt. Name );

}

}

Foreach (cubedimension _ DM in _ cube. dimensions)

{

Console. writeline ("---- dimension:" + _ DM. Name );

}

}

}

The running result is as follows:

It can be seen that an SSAs database basically has the following structure:

The corresponding part of the mining structure is omitted.

When actually processing the cube, you can add your own logic in the enumeration process to determine whether the SSAS object needs to be processed. If so, you can call the process method.

This method can also be used to indirectly implement an incremental solution. However, it requires some coordination in the data warehouse entity design. The specific solution is beyond the scope of this article, but will be mentioned later.