I’ve been building web sites for a while now. I’ve been writing code, object oriented style for the better part of a decade. Unit testing I’ve been doing for all of about 1-2 years to various degrees. Nothing super advanced yet, and slowly I’m getting some of the more advanced unit testing methods figured out. Not so much advanced, but just newer methods for testing in different situations.
So now I’ve figured out web service unit tests, database unit tests, and all sorts of other ways to unit test things. So my question is, how does one unit test a cube?
An SSAS (SQL Server Analysis Services) Cube can be built and then exported out into XMLA for running against another server to create the cube. Also it is good for a kind of backup mechanism. Cubes also can have MDX segments, which might be easier to test. But amid all of these pieces, and all of the “code” written for cubes, what is a good way to test a cube? How does one break the parts of a cube into units to be able to test in the first place?
Off hand I’m just going to trace over some ideas I stumbled into while riding the bus out to Scott Hanselman’s talk on ASP.NET MVC.
The First Unit Testing Idea for Cubes
I could develop a custom framework using ADOMD.NET that would literally test points of the cubes, dimensions, and such things like that. Adding the ability to just insert attributes onto classes that are utilizing the cube or some other such association. Building something like that would work well if one was writing code directly to the cube. However…
The Second Idea is an MDX Test Framework
This idea sounds much better, but I actually don’t have the first idea on how I would prospectively implement it. Anyone out there ever try to implement such a thing with MDX?
So that is my first two ideas… and I’ve arrived at my destination, thus am done thinking of idea for this entry.
Does anyone else have any ideas or thoughts?
I was thinking of the same thing. I was thinking of it twofold: structure and data. SSAS Cubes not only have to be unit tested for data but to make sure the code paths (hierarchies) for analysis make sense.
First, it depends on how you generate your cube. For example, if certain items in your cube are dynamic (i.e. levels): you could unit test on the database end for structure and data. Furthermore, your going to want to assert that the data from the rational engine made it into the proper places inside SSAS. For example, if you have 5,000 customers inside your source database and have 4,500 in your SSAS cube you probably are doing something wrong (unless the 500 have a business case for not being there).
Checking data is tricky. It depends on your environment and what you do. However, I have found that writing (like you mention) an MDX generator and then making sure the data is 100% is hard. However, doing a reasonableness check isn’t. You can use Excel 2007/Panorama to generate the MDX for you, by sitting a analyst down and doing what needs to happen..then you can use that MDX for future cube builds and:
a) check for failure of the MDX
b) do a check for feasability (i.e. profits grew 5000% over the last month is probably not right)
One thing that is a subtle point that you make without you saying it. With the growth of BI and especially MS’s tools…there is a big potential here for some commercial applications that are missing in the BI space. I can see companies that formed from the popularity of .NET/SQL Server (i.e. Dundas, Red Gate), in the same way have big potential in the SSAS market.
Yeah. That is what I’m starting to think… that there are some serious spaces for software just like you mention in the SSAS market.
Which really makes me think that might be my next project space. 🙂
I also thought about it. A lot.
I agree with Bart. You can check the structure of your dimensions and the data itself. I don’t think that making a tool that will generate MDX and SQL queries will be so hard to do. The tool will randomly select a tuple in the cube and will generate a proper MDX & SQL queries. It will send these two queries to the multi-dimensional and to the relational DBs and will compare the results. This process will run on daily basis, not only after development.