Setting Data source for XMLA provder

[hamish]

I have OLAP Cube defined in SQL Server Analysis server and now am trying to connect the RadpIvotGrid (for WPF) and RadPivotFieldList to this Cube. I presume I should create a xmlaDataProvider and then set the connection settings. I cannot seem to get this working nor can I find any detailed instructions. My specific questions are:
1. How do I format the connectionsettings.ServerAddress - do I have to use http: or can I just provide a connection string. Can you provide examples of both of these - my server name is CUBESERVER and its ip address is 192.168.1.117
2. Once I have got a connection I presume I just set the RadPivotGrid and RadPivotFieldList dataprovider to the xmlaDataProvider created in step 1 and 2 above - is this correct:?
3. How then do I specify what fields I want to show in the PivotField List or does it just pick these up from the dataprovider - i.e. the fields in the Cube and what does it pick up - e.g. The measures or the dimensions?
4. Finally do you have any example code (in C#) of connecting to a SQL Server Analysis server and setting the connection etc

I would appreciate your comments - thank you

[Rosen Vladimirov]

Hello Hamish,

Thank you for your interest in RadPivotGrid. I'm going straight to your questions:

1. The connection settings depends on the provider that you want to use - we give you two options - XmlaDataProvider and AdomdDataProvider. XmlaDataProvider is used when you want http connection to your server. XmlaDataProvider requires specific configuration and the connection is made through msmdpump.dll. You can check this article in our online help which explains how you can set http connection to your cube. Once you have setup the http access, your connection string will look like this:

<pivot:XmlaDataProvider x:Key="DataProvider">

    <pivot:XmlaDataProvider.ConnectionSettings>
        <pivot:XmlaConnectionSettings  Cube="Adventure Works" Database="Adventure Works DW 2008R2"  ServerAddress="http://demos.telerik.com/olap/msmdpump.dll"/>
    </pivot:XmlaDataProvider.ConnectionSettings>
</pivot:XmlaDataProvider>

As you can see, the ServerAddress uses http connection and the mentioned msmdpump.dll.

In case you want to use AdomdDataProvider, there's no need to setup http access. AdomdDataProvider uses Microsoft.AnalysisServices.AdomdClient.dll version 10. You can check if you have it installed, it should be under the following directory: C:\Program Files (x86)\Microsoft.NET\ADOMD.NET\100. If you cannot find it there you can install Adomd library from here - click on the Download button and check SQLSERVER2008_ASADOMD10_x86.msi (or the version for your PC). Once you do this you'll be able to use AdomdDataProvider in the following way:

<pivot:AdomdDataProvider x:Key="ADOMDDataProvider" >
    <pivot:AdomdDataProvider.ConnectionSettings>
        <pivot:AdomdConnectionSettings
            Cube="Adventure Works"
            Database="Adventure Works DW 2008R2"
            ConnectionString="Provider=MSOLAP.5;Data Source=ServerName;Initial Catalog=Adventure Works DW 2008R2;User Id=domain\user;Password=myPassword;">
        </pivot:AdomdConnectionSettings>
    </pivot:AdomdDataProvider.ConnectionSettings>
</pivot:AdomdDataProvider>

the UserId and Password are not mandatory and their usage depends on your SQL Server settings. As you can see - int the name of the server is passed to Data Source parameter. Please note that you have to set the name of the Database in both Initial Catalog (part of the ConnectionString) and Database (property of AdomdConnectionSettings).

2. Once you have set the provider, you just have to set it to RadPivotGrid and RadPivotFieldList:

<pivot:RadPivotGrid x:Name="Pivot" DataProvider="{StaticResource DataProvider}" />
<pivot:RadPivotFieldList x:Name="FieldList" Grid.Column="1" Margin="4 0 0 0" DataProvider="{StaticResource DataProvider}"/>

3. RadPivotFieldList will show all dimensions, measures and KPIs that are available for your Cube.

4. You can check this article which shows how to use XmlaDataProvider in Xaml, C# and VB. This article will show you how to set AdomdDataProvider in Xaml, C# and VB. Also you can check the articles for the two providers under the Features section of RadPivotGrid in our online help.

Hope this helps. Feel free to contact us in case you have any problems or concerns.

Regards,
Rosen Vladimirov
Telerik