The general steps for developing a Microsoft Analysis Services Klip are the same as Klips using any other supported types of data source. That is:
1. Specify the location of the source (connectionstring and query) using XML or JavaScript.
2. Write the <style> section that matches tags in the returned XML data. Klipfolio Dashboard uses this information to display the data in your Klip. The tags you use here are case-sensitive.

Note: Your stylesheet must match your own data.
There is a sample Microsoft Analysis Services Klip using XML and another using JavaScript available for your reference. These sample Klips are designed to work with the Analysis Services Tutorial data set that comes with Microsoft SQL Server 2005. Be sure to modify the Klips according to your own internal server setup in order to run them.

If you are already familiar with developing an ODBC Klip, you may simply refer to the Important notes section below to get the information about the connection string and query that are specific to this case.

If you have never written a Klip before, we suggest that you read through this entire article.

For more information on Microsoft Analysis Services and MDX, see:
http://en.wikipedia.org/wiki/Microsoft_Analysis_Services
http://en.wikipedia.org/wiki/Multidimensional_Expressions

Important notes

It is crucial that the following 4 points be satisfied in order for a Microsoft Analysis Services Klip to work:
1. Correct connection string for MSOLAP
2. Correct permissions for the users
3. Correct query
4. Correctly formatted XML and stylesheet

1. Correct connection string for MSOLAP
In this article and in the example Klips, the following connection string syntax is used:
Provider=MSOLAP.3;Data Source=myservername;Initial Catalog=Analysis Services Tutorial;Integrated Security=SSPI

Note that there is different behaviour between the different providers MSOLAP.3, MSOLAP.2, and MSOLAP in terms of the sort of errors an incorrect connection string might cause. (Error messages can be output only when using JavaScript to specify the query and connection strings in your Klip. See Should I use XML or JavaScript? below for details.)

For example, under MSOLAP, "Connection reset by peer." errors may occur, while under MSOLAP.2 and MSOLAP.3, errors may say "Cannot connect to database DATABASE. Either the user DOMAINUSER does not have permission to access this database or the database is not running."

These errors are the result of incorrect permissions set for the current logged in user on the MSSQL server instance. Which brings us to the next point:

2. Correct permissions
It is imperative that the correct permissions on the database be granted for the domain user attempting to connect under SSPI. You can reproduce this by trying to use Excel 2007 to connect to an Analysis Services data source from the same user's machine. If you see similar errors to the ones mentioned above, then it's most likely a permissions issue. The granting of the correct permissions is independent of the Klipfolio Dashboard application and should be corrected in order for the Klips to work.

3. Correct query


SELECT [Measures].[Internet Sales-Sales Amount] ON COLUMNS,
GENERATE (TOPCOUNT ([Customer].[Customer Geography].CHILDREN, 5, [Internet Sales-Sales Amount]),
({[Customer].[Customer Geography].CURRENTMEMBER}, TOPCOUNT ([Product].[Product Line].CHILDREN, 10, 
[Internet Sales-Sales Amount])), ALL) ON ROWS FROM [Analysis Services Tutorial]

This query generates the necessary repeating elements in the response from the database to set up a drill down Klip. It looks something like this:


United States -> Road -> $xxx
United States -> Mountain -> $xxx
United States -> Touring -> $xxx
Australia -> Road -> $xxx
Australia -> Mountain -> $xxx
Australia -> Touring -> $xxx

...where $xxx is the [Measures].[Internet Sales-Sales Amount] for each of the combinations.

This allows the [Customer].[Customer Geography].CHILDREN to be aggregated as the Klip's drilldown level 1 (as seen in the sample Klips) and provides a second level of drilldown for the [Product].[Product Line].CHILDREN.

(For more on drill down Klips, refer to the how-to article, Displaying values in a Drill Down Klip.)

4. Correctly formatted XML and stylesheet

The result of a correct query should return the data in the following form:


<xml xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'
    xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882'
    xmlns:rs='urn:schemas-microsoft-com:rowset'
    xmlns:z='#RowsetSchema'>
    <s:Schema id='RowsetSchema'>
        <s:ElementType name='row' content='eltOnly'>
            <s:AttributeType name='c0' rs:name='[Customer].[Customer Geography].[Country-Region].[MEMBER_CAPTION]' rs:number='1'>
                <s:datatype dt:type='string' dt:maxLength='4294967295' rs:long='true'/>
            </s:AttributeType>
            <s:AttributeType name='c1' rs:name='[Product].[Product Line].[Product Line].[MEMBER_CAPTION]' rs:number='2'>
                <s:datatype dt:type='string' dt:maxLength='4294967295' rs:long='true'/>
            </s:AttributeType>
            <s:AttributeType name='c2' rs:name='[Measures].[Internet Sales-Sales Amount]' rs:number='3'>
                <s:datatype dt:type='string'/>
            </s:AttributeType>
            <s:extends type='rs:rowbase'/>
        </s:ElementType>
    </s:Schema>
    <rs:data>
        <z:row c0='United States' c1='Road' c2='4322438.4058007635'/>
        <z:row c0='United States' c1='Mountain' c2='3547956.7750005559'/>
        <z:row c0='United States' c1='Touring' c2='1302225.5399999938'/>
        <z:row c0='United States' c1='Accessory' c2='217168.78999998758'/>
        <z:row c0='United States' c1='Components'/>
        <z:row c0='Australia' c1='Road' c2='5029120.4058005875'/>
        <z:row c0='Australia' c1='Mountain' c2='2906994.448600261'/>
        <z:row c0='Australia' c1='Touring' c2='997757.11999998731'/>
        <z:row c0='Australia' c1='Accessory' c2='127128.61000000588'/>
        <z:row c0='Australia' c1='Components'/>
        <z:row c0='United Kingdom' c1='Road' c2='1610247.364299997'/>
        <z:row c0='United Kingdom' c1='Mountain' c2='1185550.4065999871'/>
        <z:row c0='United Kingdom' c1='Touring' c2='528278.10999999696'/>
        <z:row c0='United Kingdom' c1='Accessory' c2='67636.329999998939'/>
        <z:row c0='United Kingdom' c1='Components'/>
        <z:row c0='Germany' c1='Road' c2='1390063.2491999916'/>
        <z:row c0='Germany' c1='Mountain' c2='1021094.3289999882'/>
        <z:row c0='Germany' c1='Touring' c2='428772.46999999817'/>
        <z:row c0='Germany' c1='Accessory' c2='54382.289999999455'/>
        <z:row c0='Germany' c1='Components'/>
        <z:row c0='France' c1='Road' c2='1323295.8034999929'/>
        <z:row c0='France' c1='Mountain' c2='917158.25079998968'/>
        <z:row c0='France' c1='Touring' c2='348562.44999999809'/>
        <z:row c0='France' c1='Accessory' c2='55001.209999999453'/>
        <z:row c0='France' c1='Components'/>
    </rs:data>
</xml>

If using XML to specify the connectionstring and query, this data is not visible to you. If you use JavaScript, you can use traceln() to output the returned data as shown above in the Debug Window. To decide whether to use XML or JavaScript, see the next section.

Either way, the stylesheet must be written to match your returned data. See the Stylesheet section below for details.

Should I use XML or JavaScript?

To decide, consider the following:
XML:

You simply add <odbc>, <connectionstring> and <query> parameters in the <contentsource> block. Klipfolio Dashboard uses that information to get the data from your database in XML, and displays it according to your stylesheet.
Use XML parameters if your connection string and query string will remain static throughout the use of the Klip.
If you run into problems and are unsure what is causing the Klip to not work as expected, it is best to use JavaScript, as it offers ways for you to output errors and debug statements into the Debug Window.

JavaScript:

You write the entire steps from setting the connection string and query to getting the returned data from your database in XML format using Engines.ODBC and Engines.ODBC.Response APIs.
Use JavaScript if you want to set your connection string and/or query dynamically. This would be useful when, for example, you want to ask your users to supply their own query strings through a search textfield.
You are able to use the traceln() function to output errors and debug statements into the Debug Window.

Examples

In the examples below, these sample strings will be used:
onnection string:
Provider=MSOLAP.3;Data Source=myservername;Initial Catalog=Analysis Services Tutorial;Integrated Security=SSPI
query:
SELECT [Measures].[Internet Sales-Sales Amount] ON COLUMNS,
GENERATE (TOPCOUNT ([Customer].[Customer Geography].CHILDREN, 5,
[Internet Sales-Sales Amount]), ({[Customer].[Customer Geography].CURRENTMEMBER},
TOPCOUNT ([Product].[Product Line].CHILDREN, 10, [Internet Sales-Sales Amount])),
ALL) ON ROWS FROM [Analysis Services Tutorial]

XML example

1. Specify the connection string and query for your database in the <contentsource> block:

<html>

<contentsource>
    <odbc>
        <connectionstring>Provider=MSOLAP.3;Data Source=myservername;
        Initial Catalog=Analysis Services Tutorial;
        Integrated Security=SSPI</connectionstring>
        <query>SELECT [Measures].[Internet Sales-Sales Amount] ON COLUMNS,
        GENERATE (TOPCOUNT ([Customer].[Customer Geography].CHILDREN, 5, [Internet Sales-Sales Amount]), ({[Customer].[Customer Geography].CURRENTMEMBER}, TOPCOUNT ([Product].[Product Line].CHILDREN, 10, [Internet Sales-Sales Amount])), ALL) 
        ON ROWS FROM [Analysis Services Tutorial]</query>
    </odbc>
</contentsource>
</html>

2. Skip to the Stylesheet section below.

JavaScript example

1. Leave the <contentsource> block empty or remove it entirely, as it will not be used.
2. Add <klipscript> to get the XML version of the data from your database as follows:


var odbc = 0;

function onRefresh () {
    var query_resp = 0;

    if (!odbc) {
        // Set the connection string. Update it according to your system setup.
        odbc = Engines.ODBC.newDataSource("Provider=MSOLAP.3;Data Source=myservername;Initial Catalog=Analysis Services Tutorial;Integrated Security=SSPI");
    }

    // If connection to the database is successful, proceed with the query
    if (odbc.connect()) {

        // Set up the database object with a query
        odbc.setQuery(
            "SELECT [Measures].[Internet Sales-Sales Amount] ON COLUMNS, " +
            "GENERATE (TOPCOUNT ([Customer].[Customer Geography].CHILDREN, 5, [Internet Sales-Sales Amount]), ({[Customer].[Customer Geography].CURRENTMEMBER}, TOPCOUNT ([Product].[Product Line].CHILDREN,10,[Internet Sales-Sales Amount])),ALL) ON ROWS " + 
            "FROM [Analysis Services Tutorial]"
        );

        if (odbc.sendQuery ())
        {
            // If the query is successfully sent, get the response object in XML format
            query_resp = odbc.response.xml;

            // Output the returned data in XML format in the Debug Window
            // traceln( query_resp);
        }

        // If sending of the query to the database fails, output the error
        else {
            traceln( "Could not send the query: " + odbc.getLastError() );            
        }

        // Disconnect from the database
        odbc.disconnect ();
    }    

    // If connection to the database fails, output the error
    else {
        traceln( "Could not connect to database: " + odbc.getLastError() );            
    }

    // Return the XML data for Klipfolio Dashboard to parse using <style> settings
    return Engines.Data.process (query_resp);
}

3. See the Stylesheet section below.

Stylesheet

Whether you use <contentsource> or <klipscript>, the returned data in XML format looks exactly the same as described in point 4 under the "Important notes" section above.

Your stylesheet will then follow this format:


<style>
    z:row::attribute(<name of column header 1>) {
        ...
    }
    z:row::attribute(<name of column header 2>) {
        ...
    }
    ...
    z:row {
        type: item;
        definition: all;
    }
</style>

Note that the type: item definition must come after the entry definitions. For example, using the sample data, the stylesheet block would look like this:


<style>
    z:row::attribute(c0) {
        type: enum;
        itemcol: 1;
        drilldown: 1;
    }
    z:row::attribute(c1) {
        type: enum;
        itemcol: 2;
        drilldown: 2;
    }
    z:row::attribute(c2) {
        type: number;
        itemcol: 3;
    }
    z:row {
        type: item;
        definition: all;
    }
</style>

Note that the attribute names (c0, c1, c2) are case-sensitive, and no spaces should be contained inside the parentheses.

(For more on getting data from complex XML, also see the how-to article, Extracting data from inside an XML tag.)

Troubleshooting - why does my Klip say "No data"?

If your attempt at building a Klip to connect to Microsoft Analysis Services results in "No data." there are many possible explanations. Here are some possible issues with suggestions on how to resolve them:

An incorrect connection string (wrong server location, wrong authentication method, wrong provider, wrong initial catalogue)
Test using Excel or another software system capable of connecting to MSOLAP / MSSQL first to ensure connection string is correct.

An unauthorised end user (not assigned rights on the database)
Test using Excel or another software system capable of connecting to MSOLAP / MSSQL first to ensure a connection is possible.

An incorrect query
Test using Visual Studio / SQL Server Manager to ensure you are obtaining the correct results for your query and user.

An incorrect use of CSS in the <style> block in your Klip file to process the XML obtained
Test using a Klip with JavaScript to view odbc.response.xml where odbc is the data source object created. Use traceln() as shown in the sample Microsoft Analysis Services Klip using JavaScript, then make sure that the <style> block is written as explained in the Stylesheet section above. Also see Extracting data from inside an XML tag.

To leave a comment, you must sign in or register (it's free).


Comment Type:
Title:
Comment:

Working with Microsoft Analysis Services (SSAS) / MSOLAP / MDX Queries

Problem:

Solution:

Sample Klips

Overview