Category Archives: 13292

Cracking The Code



Excel’s ability to run pivot tables against an OLAP cube in Analysis Services is a truly wondrous beast. The ease of use; volumes of data; the filtering; all of these provide a rich environment for users to easily inspect their data and get real information out. Add to this that Microsoft seem to believe that pivot tables are a winner and keep improving it, access to cubes and slicers being some of the latest significant changes, I can see no reason for all serious Excel users not to get to grips with this technology.

In addition to all of this goodness, it is possible to build a table query against the cube using the cube formulae. Sometimes this might be the appropriate way to present your information. But cube formulae are hard, the syntax is pure MDX, and long-winded. For example, using the MDX cube from the SQL Server MDX Step By step book that I used in my last post, a cube formula to get the Reseller Sales Amounts for Cleaner products in 2003 within the US is

=CUBEVALUE(“MDX SBS SBS”,”[Date].[Calendar].[Calendar Year].[CY 2003]“,”[Product].[Product Categories].[Subcategory].[Cleaners]“,”[Measures].[Reseller Sales Amount]“,”[Geography].[Geography].[Country].&[United States]“)

Phew! That is hard work. Imagine repeating that for say years by product category, maybe a 5×20 table of values.

I tend to create simple macros that add lists of available members to a dropdown in the ribbon, a user can pick a business oriented item from this list, and it updates the activecell formula to add the appropriate MDX. This way, a user has a simple way to build the cube formulae, and will see the MDX so they can learn if they are so inclined.

In addition, we can be smarter and simplify the formulae, because each row would use the same product sub-category, each column would use the same calendar year. We can use the CUBEMEMBER function to get the row and column heading values, such as

=CUBEMEMBER(“MDX SBS SBS”,”[Date].[Calendar].[Calendar Year].[CY 2003]“)

and use that within the value formula,

=CUBEVALUE(“MDX SBS SBS”,I$5,”[Product].[Product Categories].[Subcategory].[Cleaners]“,”[Measures].[Reseller Sales Amount]“,”[Geography].[Geography].[Country].&[United States]“)

Similarly, we can use CUBEMEMBER to get the Cleaners member

=CUBEMEMBER(“MDX SBS SBS”,”[Product].[Product Categories].[Subcategory].[Cleaners]“)

and use that within the value formula,

=CUBEVALUE(“MDX SBS SBS”,I$5,$H6,”[Measures].[Reseller Sales Amount]“,”[Geography].[Geography].[Country].&[United States]“)

Finally, the US member

=CUBEMEMBER(“MDX SBS SBS”,”[Geography].[Geography].[Country].&[United States]“)

and use that within the value formula,

=CUBEVALUE(“MDX SBS SBS”,I$5,$H6,”[Measures].[Reseller Sales Amount]“,$H$3)

So far, so good, but how are we supposed to know what those members are, and how to get the full MDX syntax of the member items? Of course, if we know the cube structure intimately and know MDX syntax thoroughly, we have no problems, but most users are not as informed.

Yet again, Excel comes to our rescue, in that it will tell us the cube formulae.

If we take the pivot table that we showed in the last post again, a table of product sub-categories over calendar years for a particular region, Figure 1, which just happens to match up to the example above, we can demonstrate how



Figure 1


If we go to the PivotTable Tools context ribbon, and on the Options tab, Tools group there is an OLAP Tools dropdown. The final item on this dropdown is ‘Convert to Formulas’, Figure 2, which will change the pivot table to a table of cube formulae.



Figure 2


When you select this option, you will get the dialog shown in Figure 3 which allows you to keep any report filters or convert them.



Figure 3


In this example, I retained the report filters, and the result was a table as shown in Figure 4.



Figure 4


If we examine this table, we can see the cube formulae that generate the table, Figure 5 highlighted shows a particular cube member, and the MDX that is required. This could be the end result, we might just use the table as presented, or it can be used as a learning tool, to familiarise with the cube and the MDX syntax.



Figure 5


Whilst this is a very handy trick for getting to understand the MDX required for generating the cube formulae, it is on an individual cell basis. The pivot table itself does not pull all of this data back on a cell by cell basis, it issues a far more complex MDX set query that pulls back all of the data specified in your pivot table. To see this code would be very useful in gaining better MDX understanding, so how can we do this?

There is nothing built into Excel to provide this, but there is a free addin at CodePlex called OLAP PivotTable Extensions which adds a pivot table context menu item. One of the extensions is to show the MDX code. If you right click within the pivot table, you should see the new context menu item, as in Figure 6.



Figure 6


This displays the following dialog, Figure 7, where you can see the MDX code on the MDX tab.



Figure 7


As you can see at first sight, this is quite scary, but most of it is a list of cells being returned, such as [Product].[Product Categories].[Product].[List Price]. Cut and paste it to a text editor and you should be able to format it and make it more readable, helping to understand what it is doing.

One thing to note in this query, as in most pivot table queries off a cube, is that is uses the NON EMPTY clause in the selects. What this effectively does is to outsort any of the member combinations along a particular axis that have nothing but empty cells. At first sight this seems like a good idea, don’t clog up the query with a whole bunch of rows where there is no data. But there is a potential downside to this. What if you want to see some of those empty rows? As in the case where some of your sales team might have targets and some don’t (for instance, customer salesmen as against store salesmen). In this case, you would want to see their empty rows because you would want to know that they are not hitting those targets, but you would not want to see the empty rows for the store sales staff. In an MDX query in SMS you would use the Exists function instead of NON EMPTY, I have yet to find out how to do the same in a pivot table.

The XML Is On The Kid















I recently wrote about a database tool that I have built to facilitate adding stored procedures to databases, SP Builder.


 


XL-Dennis made a couple of comments, one of which was with regards to the medium used for the script file. He talked about XML files, and whilst I had used an INI file when I originally built it I had thought about using XML but dismissed it at the time as my XML skills and knowledge were minimal to put it mildly (oddly, I have been aware of and using XML for over 12 years now, mainly as a consumer of such, but I still feel it promises far more than it delivers as a technology).


 


XL-Dennis’ comment gave me the prod I needed to actually do something about this, so I set about converting my tool to using XML files.


 


This is the structure of the XML I designed, using the same data as my previous posts.


 


<?xml version=”1.0″ encoding=”utf-8″?>
<database type=”Access”
         path=”C:\MyDatabases\”
         name=”MyDB.mdb”>

   <category name=”Get”>
 
       <procedure name=”spGetCompanyGoals”>
       
           <SQL code=”SELECT SUM(RD.SalesGoal) AS ‘Company Sales Goal’, ” />
           <SQL code=”      SUM(RD.BonusGoal) AS ‘Company Bonus Goal’ ” />
           <SQL code=”FROM refUsers AS RU ” />
           <SQL code=”WHERE LoginID = prmLoginId;” />
       </procedure>
 
       … more procedures
   </category>
 
   <category name=”Delete”>
 
        <procedure name=”spDeleteSalesType”>
 
           <parameter name=”prmUpdatedBy”
                      type=”VarChar (50)” />
 
           <parameter name=”prmSalesTypeID”
                      type=”Integer” />
           
           <SQL code=”UPDATE  refSalesTypes               ” />
           <SQL code=”SET     Deleted = TRUE,             ” />
           <SQL code=”        UpdatedBy = prmUpdatedBy,   ” />
           <SQL code=”        UpdatedOn = Now>             ” />
           <SQL code=”WHERE   SalesTypeID = prmSalesTypeID;” />
       </procedure>
 
       … more procedures
 
       … more categories
   </category>
</database>


 


There are some distinct advantages to the XML to my mind. These are:


  • it is more readable
  • there is less meta-information, such as the various counts, because the XML parser will provide all of that to the code
  • the XML parser provides a level of validatio
  • tools such as Altova XMLSpy provide a much better IDE for creating and updating these files than a text editor, as well as validation

 


There are some disadvantages of course, but the only one that really irks me is having to use &amp; for & and so on. I understand why I have to, but it still rankles a tad.


 


In the XML design, there were a couple of decisions to be made, around the elements and attributes. Before I embarked upon the design I read the chapter on XML in the second edition of the indispensable Professional Excel Development, and whilst it suggested encapsulating the data in elements rather than attributes I chose to use attributes as I felt it was more readable that way, attributes still need to be within a parent element which loses clarity. In my mind, this


 


           <parameter name=”prmSalesTypeID”
                      type=”Integer” />


 


is far simpler than this


 


           <parameter>
               <name>prmSalesTypeID</name>
               <type>Integer</type>
           </parameter>


 


Changing the addin was trivial once I had worked out the code for reading the XML file, updating the INI file to my XML format took far more time. The XML version ofthe addin can de downloaded here.


 

 


SP Builder is supplied as an Excel 2003 addin, or XLA file, and adds a menu option to the Tools menu with three options, Build SPs, SP Builder List and About.





 


As you many know, when Excel 2003 workbooks that create commandbars are opened in Excel 2007 or 2010 the menus are accessed via the Addins tab. This is not a satisfactory solution, so the addin tests the Excel version, and if it is 2003 or earlier it builds the commandbars, it is 2007 or 2010 it loads a ‘light’ 2007/2010 addin that adds a group to the Developer tab.



 





This way, we have a single addin that runs in any version of Excel.


 


 


One thing that I found was that I could not insert comments in my file, the parser failed when I had comments. I used what I believe is the correct format for comments, that is


<!—
‘———————–
‘ Check Stored Procedures
‘———————–
–>


but it only worked when I completely stripped out the comments.


 


I also created an XSD file for the XML, which I used to play with reading it into Excel, but I see no real use for Excel in this process other than hosting the code, so that is going nowhere. Is there any other use I can use the XSD for? This also points at the next step, take Excel out of the process completely and create a standalone VB application; that would make a nice candidate for me to develop some more .Net skills.