page static_html.xsl b2005_11_21

Setting up SQL 2005 and VS 2005

MSDN downloads

I began this journey by downloading from MSDN the DVD ISO images for SQL 2005 Developer Edition and for Visual Studio .NET 2005 Professional. Each is about 2.5GB and over my not-so-fast DSL this download took about 30 hours. Amazingly all those bits did arrive in order. Instead of burning a DVD from the downloaded ISO images, I used the “Virtual CD-ROM Control Panel v2.0.1.1” from Microsoft, hoping that it could handle DVD images. It does.

I first installed VS.NET which went without any problems. But I found that the default installation included the SQL Express Edition. I unistalled this since I didn’t know how it might interfere with the installation of SQL 2005 Developer Edition.

Next I installed SQL 2005 Developer Edition and specified that pretty much everything should be installed to disk. I specified the SQL instance as SQL2005 since I also have SQL2000 and AS2000 on the machine. I didn’t find that I had any sample databases installed - perhaps I did something wrong. Well not to bother. I found the sql scripts in C:\Program Files\Microsoft SQL Server\90\Tools\Samples\AdventureWorks OLTP and C:\Program Files\Microsoft SQL Server\90\Tools\Samples\AdventureWorks Data Warehouse

Running the scripts in Management Studio resulted in empty databases. I found in each the comment about changing $data_path. It would have been easier had I read “Reinstalling Sample Databases from Script” in books online where it says in step three to take care of $data_path.

Now I have sample databases which I can query. Cool.

Where’s the cube?

Gotta have a cube to play with XMLA - where is it? All I could find that looked applicable was

C:\Program Files\Microsoft SQL Server\90\Samples\Analysis Services\Programmability\AMO\AMOAdventureWorks\CS\ AmoAdventureWorks.sln. It looked like it would build a cube - and it did. But something didn’t smell right. I opened the readme and found “This sample creates a subset of the functionality of the AdventureWorks cube located in the AdventureWorks Analysis Services Project.” Only problem is I don’t have such a project.

I mounted the DVD again and took another peak at SQL Server x86\Tools\ReadmeSQL2005.htm. And found this tidbit:

Note:

The Completing Microsoft SQL Server 2005 Setup page, which displays at the end of Setup, contains incomplete instructions for installing the sample databases and samples. Follow the instructions below instead.

I followed the provided instructions and found the folder C:\Program Files\Microsoft SQL Server\90\Tools\Samples\AdventureWorks was added. So I ran the enterprise version of the solution. It bombed out at first because the connection string and server were set to the default (localhost). I changed these to my instance (PE700\SQL2005) and then the cube built and processed and deployed sucessfully.

So now I have two AS databases - AmoAdventureWorks and AdventureWorks

Setting up XMLA for SQL 2005

In a previous ThinCMS article I had done this using Beta 1. I reviewed that article again but decided to go looking for the “official” word on how to do this for the release. I found the word in a TechNet article titled “Configuring HTTP Access to SQL Server 2005 Analysis Services on Microsoft Windows Server 2003”. Well you just won’t find more specific instructions - thanks Microsoft. Not feeling like making any decisions, I even used all of the paths and names used in this tech note.

I put http://localhost/olap/msmdpump.dll in an IE address bar and was presented with

Well that is interesting! Instead of saying GET is not supported, which is what XMLA 1.1 does, I am told that I am not using the correct GET syntax. I’ll be cheching with Akshai on that - perhaps we finally get GET in XMLA. But the main thing for now is that XMLA is indeed listening at that URL.

POSTing messages to XMLA

As always, I begin my querying of XMLA using my command-line test harness. You can download the newest version here. In my previous blog on querying Yukon XMLA I had changed the format of the SOAP message slightly which resulted in messages which worked both with XMLA 1.1 and with the new provider. This new format worked fine with the SQL 2005 release

Since most of my test invocations are against FoodMart, I migrated my SQL 2000 FoodMart cube using the Analysis Services Migration Wizard in SQL Server Management Studio. Start the Migration Wizard from an Analysis Services server node in the Object Browser in SQL Server Management Studio. You can also start the wizard at the command prompt, by running the program MigrationWizard.exe.

To see what is in the measures schema, I ran

cscript //nologo xmla.wsf /url:"http://localhost/olap/msmdpump.dll" /c:"Adventure Works DW" /d:measures /r:"catalog=Adventure Works DW" /out:discover-measures.html /v

which generated these results.

After testing the standard Discover calls and a simple MDX Execute, I wanted to move onto trying some of the new features. One of the exciting new features is KPIs. The first question I had was do these show up when I query all measures:

cscript //nologo xmla.wsf /url:"http://localhost/olap/msmdpump.dll" /c:"Adventure Works DW" /e:"SELECT {[Measures].AllMembers} ON COLUMNS, {[Geography].[All Geographies]} ON ROWS FROM [Adventure Works]" /out:"aw-measures.html" /v

This command-line invocation generated these results. And you can see that indeed the KPI measure are present. So what new MDX stuff can I throw at XMLA? Mosha has a good “what’s new in MDX” page at http://www.sqljunkies.com/WebLog/mosha/archive/2005/10/11/mdx_functions_as2005.aspx. I’m interested KPIs so I am going to try

cscript //nologo xmla.wsf /url:"http://localhost/olap/msmdpump.dll" /c:"Adventure Works DW" /e:"SELECT { [Measures].[Total Product Cost], KPIValue('Product Gross Profit Margin'), KPIGoal('Product Gross Profit Margin'), KPIStatus('Product Gross Profit Margin'), KPITrend('Product Gross Profit Margin') } ON COLUMNS, { [Employee].[All Employees] } ON ROWS FROM [Adventure Works]" /out:"aw-kpi.html" /v

This command-line invocation generated these results.

Alright. I can get KPI info via XMLA. Cool. Now what do I do with this info? Normally you would show the user some graphical symbols to indicate the state of the KPI. I sure wish I has some symbols. Perhaps Microsoft has some. I noticed when I was looking at KPI definitions in VS.NET that you can select a status indicator. The list includes things like Traffic Light and Gauge. Perhaps with luck these are here somewhere? I searched my SQL installation and found them in C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\DataWarehouseDesigner\KPIsBrowserPage\Images. I’ve seen better but they’ll do.

So now I want to change my generic XSLT to show KPIs using these indicators. How do I make the mapping? Since the indicator is selected in defining the KPI it must be stored in the schema. Searching BOL found an interesting page:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/asdai9/html/40fb5112-6a90-4455-82b3-8b6322490222.htm

There is now a MDSCHEMA_KPIS. So I added this as a new case in xmla.wsf

case "kpis": nRT.text = "MDSCHEMA_KPIS"; break;

And the command-line invocation

cscript //nologo xmla.wsf /url:"http://localhost/olap/msmdpump.dll" /c:"Adventure Works DW" /d:kpis /r:"catalog=Adventure Works DW" /out:kpi-schema.html /v

returns these results. I can filter the results by changing the restriction to

/r:"catalog=Adventure Works DW,CUBE_NAME=Adventure Works,KPI_NAME=Product Gross Profit Margin"

This returns only the KPI I need to go along with the MDX I used above.

Resources

Entry page on MSDN for SQL 2005 XMLA programming

http://msdn2.microsoft.com/en-us/library/ms187178(en-US,SQL.90).aspx