Use FusionChart to display the data of MDX query results

Not long ago, after the "Report on the BI System of Shanghai Jiao Tong University, an elite forum for informatization practitioners in 2011 Colleges and Universities" was released, several friends asked questions about data presentation, see the importance of data visualization in BI projects. One of them was a friend of Emmy who asked how FusionChart presented the result data after MDX query and asked this question in his blog. For details, see here. This is just a bit of time tonight, so I will give you a practical example to answer this question and hope to give you a reference.

 

Talking about FusionChart, you can visit the http://www.fusioncharts.com/view its products and introduction, its effect is indeed cool. If you want to apply it to our BI project, there are still many issues to consider. First, we know that FusionChart is based on flash to display data. Its data source can only be an xml file in a certain format, and the xml file format may be different for each type of chart. Fortunately, we can convert a dataset into xml to provide its data source. However, we know that in ADOMD. NET, the object that can be obtained after the MDX statement is queried can only be CellSet, not DataSet or able. Therefore, the key to the problem is how to convert a CellSet object to a able object.

 

As a matter of fact, as long as you google it, many of our predecessors have already solved the above problem and published the method of converting the CellSet object to the DataTable object. The specific code is as follows:

 

/// <Summary>
/// Convert CellSet to Table
/// </Summary>
/// <Param name = "cellset"> CellSet </param>
/// <Returns> </returns>
Private DataTable CellSetToTable (CellSet cellset)
{
DataTable table = new DataTable ("cellset ");

Axis columns = cellset. Axes [0]; // obtain the column Axis
Axis rows = cellset. Axes [1]; // obtain the row Axis
CellCollection valuesCell = cellset. Cells; // gets the set of measurement units.
// The level title of the row axis is the first column of the table
Table. Columns. Add (rows. Set. Hierarchies [0]. Caption );
// The title of each member of the row axis is changed to the column of the table.
For (int I = 0; I <columns. Set. Tuples. Count; I ++)
{
Table. Columns. Add (new DataColumn (columns. Set. Tuples [I]. Members [0]. Caption ));
}
Int valueIndex = 0;
DataRow row = null;
// Fill data in the table
For (int I = 0; I <rows. Set. Tuples. Count; I ++)
{
Row = table. NewRow ();
// The first column value of all rows in the table is the title of the corresponding row axis.
Row [0] = rows. Set. Tuples [I]. Members [0]. Caption;
For (int k = 1; k <= columns. Set. Tuples. Count; k ++)
{// Fill the values of the set of measurement value units in the table in order
Row [k] = valuesCell [valueIndex]. Value;
ValueIndex ++;
}
Table. Rows. Add (row );
}
Return table;
}

The code is simple. The principle is to traverse the tuples in the Cellset and fill them in the able.

After achieving the above difficulties, we can convert the data of the DataTable into xml and provide it to FunsionChart to display the data. Next, let's answer Emmy's questions step by step:

(1) decompress the attachment question.rar, restore the test6.abf file to SSAS, and test whether the MDX statement is correct, as shown in:

(2) Next, we create an ASP.. NET project, add the relevant flash files and js files to the solution, and write the CellSet to DataTable method into a tool class, and add to ADOMD. NET reference, as shown in:

(3) Add the following controls to the Default. aspx page. We hope to display data by selecting different flash charts:

(4) in the Post-Page code, compile the corresponding event Code. The Code is very simple, but it should be noted that FusionChart can use two methods to present data on the webpage, one is to generate a piece of JS Code, and the other is to directly generate a piece of Flash HTML code. Click Preview able and the result is as follows (is it consistent with the initial MDX test result ?) :

When different chart types are selected, different chart styles are generated. (There are many XML parameters. For details, refer to the official API description of FunsionChart ):

 

Finally, the complete case program is downloaded here. I hope you will have more exchanges!