Introduction

I started investigating the subject of compression in Analysis Services 2005 a couple of weeks ago. My customer had written an application using ADOMD.Net 9 and was asking if compression was indeed in effect, as there seemed to be quite a high volume of traffic on the network.

I therefore decided to investigate. This post is as much a reminder for me of the various steps I took as it is a discovering guide for anyone interested in SSAS compression.

This post is fairly long as it takes you trough my journey in SSAS packet analysis. If you're only interested in the conclusion, then, bearing any misconception on my part, it seems that xml compresssion does indeed work with ADOMD.Net 9.0, but not SX encoding, a.k.a. "Binary XML".

I'd be of course very interested in other people' ideas on this subject.

Server: Analysis Services server properties

Properties of interest:

Network / Responses / EnableBinaryXML: True if network responses can be encoded using SX proprietary encoding (default value)

Network / Responses / EnableCompression: True if network responses can be compressed (default value)

A detailed description of SX proprietary encoding (a.k.a. "Binary XML") can be found on Mosha's excellent web site at the following address: http://sqlblog.com/blogs/mosha/archive/2005/12/02/analysis-services-2005-protocol-xmla-over-tcp-ip.aspx

Client: ADOMD.Net connection string

Protection Level, Protocol Format, Transport Compression, Compression Level. All these parameters can be specified in the SSAS connection string. When you open a connection to the SSAS server with ADOMD.Net, there is a negociation phase between the client and the server, to determine the adequation of client requests (connection string parameters) with SSAS server capabilities (configured in the SSAS server properties pane).

Protection Level	The supported values are “None”, “Connect”, “Pkt Integrity” and “Pkt Privacy”. The values “Call” and “Pkt” are not supported. The default value is “Pkt Privacy”.
Protocol Format	Specifies the client-requested encoding format for XML messages on this connection. The supported values are: “Default”: Binary encoding. “XML”: Text encoding. “Binary”: Binary encoding.
Transport Compression	Specifies whether the messages on this connection are to be compressed. The valid values are the following: “Default”: Messages are compressed. “None”: Messages are not compressed. “Compressed”: Messages are compressed.
Compression Level	Specifies the client-requested level of compression when the value of the Transport Compression key is “Compressed”, where “0” specifies minimum compression and “9” specifies maximum compression. The valid values are integers between 0 and 9, inclusive. When the value of the Transport Compression key is not “Compressed”, the value of the Compression Level key MUST be ignored if present.

More information on connection string parameters is available here: http://technet.microsoft.com/en-us/library/microsoft.analysisservices.adomdclient.adomdconnection.connectionstring%28SQL.90%29.aspx

Environment setup

To have a look at what was going on, I needed a tool to analyze network traffic. I fired off my browser and downloaded the following free tools:

Network Monitor 3.3

Network Monitor Open Source Parsers

I also needed a test application that would allow me to modify the Analysis Services connection string on-the-fly and change various startup parameters, so I wrote a quick C# console application that connects to the AdventureWorks cube and runs an MDX query.

You will need to change the connection string to the SSAS server in the Console1Application.exe.config file to reflect your own configuration.

I have two machines for this test:

* A server machine running Analysis Services 2005 SP3 CU9 (9.0.4294) (M6500-WIN7), IP: 192.168.1.24 and Network Monitor 3.3. The AdventureWorks cube is processed and accessible on this machine.

* A client machine running my test console application (SONYX11S1-WIN7) with ADOMD.Net 9.0, IP: 192.168.1.16

Capture data

Verify that the SSAS server properties are properly set. (Server machine, Management Studio, Connect to Analysis Services, right-click on the SSAS instance name, Properties)

- Network/Requests/Enable BinaryXML = false

- Network/Requests/EnableCompression = false

- Network/Responses/EnableBinaryXML = True

- Network/Responses/EnableCompression = True

Open Network Monitor, select the networks you want to listen to (in my case, just my regular LAN card) and click on the "New capture..." hyperlink

Network Monitor can return vast amounts of data! To help us go through the volume of data, we will filter the capture on the tcp port used by Analysis Services and on the name of the machine that issues the query.

For the TCP port number, I'm using port 2382. This is because I am using an Analysis Services named instance. If you are connecting to the default Analysis Services instance, you should use port 2383 instead.

The machine that will issue the MDX queries is named SONYX11S1-WIN7.

Click on the "Capture Filter…" tab and type Contains(Destination, "SONYX11S1-WIN7")AND TCP.SrcPort=2382 in the filter expression textbox.

Press the Start button to start the network capture…

Run the Console application on the client machine. The ADOMD.Net opens a connection to the SSAS database and runs a query to retrieve the list of measures (we don’t actually care about the query itself, just that it generates some network traffic to the server).

Click the Stop button to stop the capture.

ADOMD.Net connection: Data Analysis

The figure below shows the captured data, filtered by destination and TCP port number.

In the traffic conversations pane, find the entry that captures the conversation between your server and you client machine, using port 2382.

Then, in the Frames summary pane, scroll down in the list until you find a frame that displays the type of packet exchanged between the client and the server. The identification of the packet type starts with  "application/".

The packet we are looking at is of type "application/xml+xpress", which indicates Compressed XML.

Other available types are:

application/text+xml	Regular non compressed xml data
application/xml+xpress	Compressed XML data
application/sx+xpress	Compressed SX encoded data (binary xml)

Given that our Analysis Services 2005 server is configured to return SX-encoded compressed xml packets, this would mean that, using ADOMD.Net, the packets are correctly compressed but that SX enconding does not work.

To confirm that it is a problem with the ADOMD.Net connection, and not the cube itself, let's run the same test with an Excel 2007 spreadsheet that connects to the cube using the "Analysis Services OLEDB Provider 9.0" provider. Let's see if we get proper SX-encoded compressed xml packets or not.

Excel 2007 connection: Data Analysis

Remove the filter on the TCP Port (since the Analysis Services OLEDB Provider 9.0 uses another port) but keep the filter on the client machine name. Start the network capture…

Create a Pivot table in an Excel spreadsheet that connects to the cube.

Stop the network capture and examine the captured frames. Look for the packet type.

So we find that Excel and the Analysis Services OLEDB Provider 9.0 use "application/sx+xpress" (SX-encoded compressed xml packets) whereas a regular application with ADOMD.Net 9 uses "application/xml+xpress" (regular compressed xml packets).

So it seems that SX-encoding does not work with ADOMD.Net 9.0, just compression.

Is this everyone's opinion? Speak up if you found something different!

-- Patrice.

ConsoleApplication1.zip (24.63 kb)

My current assignment involves building Analysis Services cubes from a SQL Server datamart, populated from an Oracle 10g database server, using SSIS. 

I spent some time over the last few weeks using the Microsoft connector for Oracle by Attunity (available for download here) to populate my SQL Server. In terms of performance, this new connector is a vast improvement over the old Microsoft OLEDB Provider for Oracle (MSDAORA) or the Oracle OLEDB provider (ORAOLEDB.Oracle). If you were previously using a linked server to an Oracle database to populate a SQL Server database, you definitely want to check out that connector. 

One thing that took me some time to find out was how to parameterize the sql command that you pass to the connector.
In the SQL Server connector, you can use a variable containing your view name or your sql command, and you pass this variable to database driver.  

In the Microsoft connector for Oracle by Attunity, not so obvious.. There is nothing in the screen that allows you to use a variable to parameterize your sql command.  

All you can do in this screen is choose either a table name or build a hard-coded sql command. 

After much fiddling with the interface, I finally got around to parameterize my sql command. The trick is to display the properties of the parent container (in this case, the data flow).  There, using property expressions, you can freely modify the Oracle sql command.  

Click on ‘…’ in front of “Expressions”. In the Property listbox, select SqlCommand

  Click on ‘…’ next to “Expressions” and start building your sql statement using the Expression Builder.   

Enjoy!    

 

SQL Server 2005 Books Online (December 2008)

https://www.microsoft.com/downloads/details.aspx?FamilyID=be6a2c5d-00df-4220-b133-29c1e0b6585f&displaylang=en

This release of SQL Server 2005 Books Online includes documentation updates for the SP3 release.

Mosha delivered a new version of MDXStudio (v0.4.11). Available here.

 

Analysis Services does not provide a way to sort dimension members in DESCending order, only in ASCending order.

Fortunately, this sort functionality is easy to implement, using the Data Source View.

Replace your table binding with a named query and create a new calculated column that you will use to order your dimension members.

Here is an example of such query:

SELECT DateKey, (-1)*CONVERT(int, Date, 112) as Days, Date FROM T_DATE

Where 'DATE' is your datetime column and 'DATEKEY' is your primary key.

Then, in the dimension definition, select the dimension key and set the following properties:

OrderBy: AttributeKey

OrderByAttribute: Days

 

And voilà, your dimension members are now ordered by the new attribute you created!

 

 

Sometimes, you need to change the BACK_COLOR of a cell, based on the result of an evaluation, such as 'Change color to YELLOW if Condition A is TRUE'. But what if you don't want to change the BACK_COLOR if Condition A evaluates to FALSE (and leave the back color as it was)? Surprisingly, the answer is quite simple... Just set it to NULL.

For example, change the BACK_COLOR to Yellow if the flag 'NewFact' is greater than 0, otherwise leave it alone:

BACK_COLOR(this) =

    IIF([Measures].[NewFact] > 0

    , 65535 /*Yellow*/

    , NULL /* Don't do anything */) ;

Very clean, simple and it won't mess with any pivot table formatting theme you might have chosen.. I love it!

A cool demo of Microsoft Surface showing interesting visualizations of BI data. This demo was shown at the Microsoft BI Conference in Seattle earlier this year.

Very shortly, I'll be closing the Microsoft chapter and will be entering a new and very exciting territory. I'm creating a small consulting company, specialized in Microsoft Business Intelligence designs and architectures. As you can imagine, taking this decision after having worked at Microsoft for so long was not an easy one to make. But the long and heated discussions I had with many of my friends and co-workers convinced me that this was the right choice to make.

I'm really greatful for the time I spent at Microsoft. I've had the chance to work with amazing people, both in Paris (France) and in Seattle, WA (USA). This company has taught me so much that I'll never have enough words to express my gratitude. Now is the time to return the favor and spread the word in the field about the huge potential of its platform.

Point your browser to http://www.declic-consulting.com and your RSS Reader to http://www.declic-consulting.com/blog if you'd like to stay in touch!

-- Patrice.

Declic Consulting, http://www.declic-consulting.com.

Just came across this announcement of a new Oracle connector for SQL Server 2008. This will be a boon for those wanting to bulk load data from SSIS into an Oracle database. Using the Oracle proprietary interfaces to load data should be significantly faster than using the traditional Microsoft or Oracle OLEDB providers...