| Downloads |
|---|
| 26564.zip |
An analytic application lets you facilitate an entire business process, from analysis to decision making. Such a process requires collaboration between the people who have the domain expertise and the people who make the decisions, and often many people need to share their work and conclusions. Because collaboration is an integral part of a business process, any complete analysis application should include a way to facilitate this sharing of ideas and work. SQL Server 2000 Analysis Services offers simple collaboration features in the form of what-if analysis—for example, you can use Analysis Services to perform cell-level and dimension-level write-back. But effective collaboration requires more than simple write-back capabilities; your application should support the ability to share business logic, conversation threads, and action plans. Let's examine a proposed architecture for a collaborative analytic application that uses Analysis Services and look at some source code you'll need to get started.
Sharing Business Logic
Because Analysis Services doesn't have built-in capabilities for sharing business logic, conversations, and action plans, you need to take advantage of other Analysis Services functionality to build these features. First, let's look at how to share business logic. In the following example, I use the term business logic to mean business-specific formulas such as calculated members or named sets. An example of business logic is the formula you use for determining your top 10 customers; such a formula is most likely business-specific. In some businesses, the most important criterion for determining who is a top customer might be the revenue the customer generates—in other kinds of businesses, the most important criterion might be profit or longevity.
Analysis Services can store and retrieve calculated members or named sets, but it has no facility for creating new server-based calculated members or named sets through the client-programming interface ADO MD. To create new, shared business logic on the server, you must use Analysis Manager or the administrative programming interface Decision Support Objects (DSO). (For information about Analysis Services' three programmatic interfaces, see the sidebar "Analysis Services Interfaces," page 42.) Although DSO access requires OLAP administrator privileges, you can develop a service to deploy on your application's OLAP server (or middle tier) that has OLAP administrator access. You can then make this service available to client applications so that the applications can perform actions such as creating new business logic.
Document Sharing
The next collaborative feature you might want to implement is document sharing. The documents you share can include conversation threads and action plans associated with information that your analysts discover in the cube data. For example, when an analyst is viewing profit for the last few quarters, she might discover that a particular product line is losing money. The analyst then develops an action plan associated with improving profit for that product line. After sharing the plan with others who view the same information, the analyst might also want to start a conversation thread that explores other possible improvements.
Because the analyst's action plan and conversation are associated with a certain portion of the OLAP cube, storing the information with that portion of the cube makes sense. This lets other analysts see the information only in context. You can link information to a portion of a cube through an Analysis Services feature called an action. An action is a link to information or a command that's associated with cube metadata such as a dimension, a level, or a cell. Actions are similar to calculated members and named sets in that you can't create or edit them through the client API ADO MD unless they're available only to the client that created them. In other words, you can't create shared actions. In my proposed architecture, I want to create and edit actions for use by more than one cube user, so I need to use the administrative API DSO.
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.