Browse DevX
Sign up for e-mail newsletters from DevX


Web-enable Your Business Intelligence Using XML/A and ASP.NET : Page 6

Exploit the power of XML for Analysis (XML/A) and make your data cubes available via the Web using a simple Web service, an ASP.NET Web Form, and some XSLT to format the data.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

Using the OLAPWebClient
This application consumes the Web service FoodMart2000.asmx, in the OLAPWebservices application. If you don't have a Web Reference to the FoodMart2000 Web service, add one before you continue. If you are not running OLAPWebservices on your localhost Web server, you must replace the Web Reference in the sample OLAPWebClient project so that it references the FoodMart2000 Web service on the server where you are running the OLAPWebservices application.

The Web form MDX.aspx contains three main controls:

  • a Textbox where users can enter an MDX query
  • an Execute button
  • an XML control that transforms the returned XML into HTML using the stylesheet in the OLAPGrid.xsl file.
The following code shows the Execute button's onClick event handler. Note that the code strips the XML namespaces from the returned XML. The sample transformation will not run properly unless you strip the namespaces. In this case, you don't need the. XML namespaces are used to uniquely identify elements in a scope; however as there's no possibility of element name collisions in this application, you can eliminate them and simplify the transformation task.

private void btnExecute_Click( object sender, System.EventArgs e) { // Put user code to initialize the // page here localhost.FoodMart2000 olapService = new localhost.FoodMart2000(); try { olapService.Credentials = System.Net.CredentialCache. DefaultCredentials; strXML = olapService.ExecuteMDX (txtMDX.Text); strSearch = "xmlns=\"urn:schemas-" + "microsoft-com:xml-analysis:" + "mddataset\""; strXML = strXML.Replace(strSearch ,""); Xml1.DocumentContent = strXML; } catch(Exception er) { Response.Write(er.Message.ToString()); } }

Using the OLAPGrid.xsl Stylesheet
The stylesheet in the OLAPGrid.xsl file provides almost the same functionality as xamd.xsl. It's not as robust as xamd.xsl but it has the advantage of being relatively easy to understand.

The MDX query returns not only the data but also an embedded schema which contains the number of columns, their data types, levels etc. The OLAPGrid.xsl stylesheet uses that information to determine the number of columns and rows required, and the levels of data available. Finally, it performs some calculations to transform the raw XML into an HTML grid containing the data.

Most of the stylesheet is straightforward; however some rows in the generated OLAP data are null. Therefore, you have to solve the problem of knowing that the value of a particular row is null. The stylesheet uses the value of the CellOrdinal attribute, which is not a cell sequence; it's the cell location in the grid.

The code uses the same PrintCell template found in xamd.xsl to display cell values.

PrintCell is a recursive template called for each tuple. The stylesheet passes two parameters to the template: $varStartCell and $varEndCell. These two variables define the boundary of a tuple. The stylesheet calculates the value of $varStartCell by multiplying the tuple number by the total number of columns in Axis0. The stylesheet calculates the $varEndCell value by adding one to the tuple number, and then multiplying that by the total number of columns in Axis0.

<!-- Display the cells for this tuple --> <xsl:for-each select= "//ROOT/Axes/Axis[@name='Axis1']/Tuples/Tuple"> <TR> <xsl:variable name="varTupleOrdinal" select="position() - 1" /> <!-- Count no. of tuples or columns in Axis 0 --> <xsl:variable name="varAxis0Coords" select="count(//ROOT/Axes/Axis [@name='Axis0']/Tuples/Tuple)" /> <!-- Calculate the start cell ordinal of the cells in a tuple --> <xsl:variable name="varStartCell" select="$varTupleOrdinal * $varAxis0Coords" /> <!-- Calculate the last cell ordinal of a cell in a tuple --> <xsl:variable name="varEndCell" select="($varTupleOrdinal+1) * $varAxis0Coords" /> <!-- Call the print cell template by passing the start and end cell ordinals limit of a tuple --> <xsl:call-template name="PrintCells"> <xsl:with-param name="varStartCell" select="$varTupleOrdinal * $varAxis0Coords" /> <xsl:with-param name="varEndCell" select="($varTupleOrdinal+1) * $varAxis0Coords" /> </xsl:call-template> </TR> </xsl:for-each>

The stylesheet calls the PrintCell template for each tuple, and the PrintCell template calls itself again recursively, adding 1 into $varStartCell each time. The recursive call continues to print cells until it reaches to the boundary of that tuple, which it determines by comparing the value of $varStartcell and $varEndCell value. When the value of $varStartCell becomes equal to the value of $varEndCell, the PrintCell template stops calling itself.

<xsl:template name="PrintCells"> <xsl:param name="varStartCell">0</xsl:param> <xsl:param name="varEndCell">0</xsl:param> <!-- start cellordinal must be less than end cellordinal limit --> <xsl:if test="$varStartCell &lt; $varEndCell"> <TD STYLE="background-color:#eeeecc; color:#000000;"> <xsl:choose> <!-- Find the cell which lies between start cellordinal and end cell ordinal --> <xsl:when test= "//ROOT/CellData/Cell[@CellOrdinal = $varStartCell]"> <!-- print the cell value --> <xsl:value-of select= "//ROOT/CellData/Cell[@CellOrdinal = $varStartCell]/FmtValue" /> </xsl:when> <xsl:otherwise> <!-- print null --> <I>null</I> </xsl:otherwise> </xsl:choose> </TD> <!-- This recursive template calls itself until the start cellordinal is equal or greater than the end cell ordinal --> <xsl:call-template name="PrintCells"> <xsl:with-param name="varStartCell" select="$varStartCell + 1" /> <xsl:with-param name="varEndCell" select="$varEndCell" /> </xsl:call-template> </xsl:if> </xsl:template>

I hope this article introduced you to XML/A and how you can use it. Improvements to the XML/A specifications in the near future will make it an even more powerful and scalable solution.

  1. Download the file VS7msxmlanalysis.wsdl here.
  2. Generate the C# class MsXMLAnalysis.cs by using the wsdl.exe utility that ships with both Visual Studio and the .NET Framework SDK. Typically, you'll find the utility in the folder /Program Files/Microsoft.NET/FrameworkSDK/bin on the drive where you installed the SDK. Run the utility using the following command, adding path information to the filenames if necessary. For help, see the MSDN documentation or type wsdl at the command prompt and then press Enter.

Afzal Farooqui has been developing applications for five years and is currently a Senior Technical Consultant with Alexander Consulting. He has worked primarily with Visual Basic, ASP, and Perl, and is now busy exploring.NET. You can reach him .
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date