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

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.

Previous Page

---

• Download The Code for this Article

---