Developing the Architecture
Figure 1 shows the application architecture I propose. I've shaded the portions you create. First, you create a Web service that makes available the collaboration capabilities—including creating actions, calculated members, and named sets—that you build on top of the DSO API. Then, you create a client application that uses a combination of ADO MD for normal cube access and the Web service for collaboration features. You might also use SQL Server to maintain other shared information such as the action-plan documents, conversation threads, or associated descriptions for any shared business logic.
Programming with DSO is less straightforward than programming with ADO MD because most of the metadata objects in DSO have the same type—they're MDStore objects. So, for example, if you have a database object, a cube object, and a dimension object, you declare and use all the objects as MDStore objects. Even though the underlying metadata objects are different and have different capabilities, the same methods and properties are available for all the objects. Depending on the metadata object you're using, some methods and properties won't work or will function differently than the same function on another object. SQL Server Books Online (BOL) has relatively few programming examples for DSO, so learning to use DSO on your own can be challenging.
Because I propose that you build the collaboration service as a Web service, you might choose to use Visual Studio .NET to build this software component. Visual Studio .NET has integrated features that are helpful for constructing Web services. The complete process of creating this application is beyond the scope of this article, but let's take a closer look at how to create the part of the application that uses DSO. To help you get started, I developed a simple C# test application that uses DSO to make a calculated member. Listing 1 shows the C# source code for this test application. I created a Windows application project in Visual Studio .NET to develop and test this source code, and I recommend that you do the same because a Windows application is a simple environment in which to develop and debug. After you've written and debugged your code, you can create a Web service project and copy the debugged source code into it.
The code in Listing 1 connects to an OLAP server and finds the FoodMart 2000 Sales cube, which has an associated list of commands. These commands are textual MDX commands that Analysis Services executes for each client when the client establishes a session. The code creates a new command and sets its Statement property to the MDX command that's required for creating a new calculated member. The code then calls the cube's Update() method to save the changed list of commands to Analysis Services' repository. Next, the code closes the Server connection.
After the Server connection closes, the code issues a method call to ReleaseComObject. I discovered that this method call is necessary after I debugged an error in my sample application. The .NET development environment has lazy garbage collection, which means that .NET doesn't always release objects immediately when they're no longer needed. When you're using COM objects such as the DSO library, lazy garbage collection can cause problems if the COM object waits until it's released to do final cleanup. I discovered this problem in my sample application when I started seeing an access violation error every time I exited my C# test program. I finally realized that I needed to explicitly tell the .NET runtime library to release the DSO Server object; after I made the change, the problem went away.
Simplifying the Guesswork
Because BOL includes so few DSO programming examples, creating actions and calculated members might require that you do some guesswork. Here's a trick to make these jobs easier. Create the calculated member, action, or named set through Analysis Manager and use DSO to display the correct MDX statement syntax. To view the MDX syntax for commands you created from Analysis Manager, uncomment the foreach loop in Listing 1. When the loop executes, it uses the MessageBox.Show() method to display all the commands that are already associated with dsoCube.
Much debate and confusion exist about what an analytic application really is, even though analytic applications have received plenty of promotion. Nearly every vendor in the business intelligence (BI) market offers analytic applications, and each vendor has its own definition of what an analytic application is. My definition is that an analytic application facilitates a business process, starting with analysis and ending with decisions. If my definition is accurate, then collaboration is a crucial part of an analytic application because you can't make decisions without the involvement of knowledge holders, stakeholders, and decision makers. Although Analysis Services doesn't specifically offer all the collaboration features you need for an effective analytic application, you can easily create collaboration capabilities in your analytic application by building on the features that Analysis Services does provide. So, when you need to add collaboration features to your next analytic application, use the information in this overview to help you plan your design.