none
Create an offline olap cube using any relational data source.

    Question

  • Hi,

    I was wondering how can you generate an olap cube programmatically in c# using any relational datasource without using analysis services. A concrete example (something i couldnt find anywhere on the net.)would be appreciated a lot. 

    Saturday, May 04, 2013 2:22 PM

Answers

  • Hi Piotr, 

    Is there a case where you can build programmatically the XMLA directly from the relational model? 

    Hi SpyrosSavvides,

    XML for Analysis (XMLA) is a SOAP-based XML protocol, designed specifically for universal data access to any standard multidimensional data source that can be accessed over an HTTP connection.

    For more information, please see:
    Developing with XMLA in Analysis Services: http://technet.microsoft.com/en-us/library/ms186654.aspx

    Regards,


    Elvis Long
    TechNet Community Support

    Wednesday, May 15, 2013 2:20 AM
    Moderator

All replies

  • Analysis services is the engine for all cube calculations, so you will have to write significant part of the SQL Server yourself.

    I don't think that it is worth the effort, why do you need it?

    You can look for some open source OLAP, like http://mondrian.pentaho.com/ .

    Also, you can't just create cube for any relational database, you need to map it to dimensional model.

    To do it right some knowledge about meaning of the data is required.

    Saturday, May 04, 2013 2:40 PM
  • Hi Piotr and thanks for the reply. 

    It is a requirement for a client. There is an article suggesting this option by using the ADOMD.Net object model. However it is required to build the XMLA script manually. This is the link if you want to express your opinion. 

    http://netpl.blogspot.com/2008/06/complete-olap-infrastructure-without_27.html

    I have removed the hyperlink since my account is not verified yet. Please copy and paste.

    Saturday, May 04, 2013 4:45 PM
  • To auto generate cube (online or offline) you need to convert relational model to dimensional model and such conversion requires more information that relational model contains. I doubt that automated conversion may produce meaningful results for anything except simplest cases.

    Simplest way to manually create XMLA is to create cube in full SSAS installation using BIDS and export it, but such approach will require SQL server license for anybody using offline cubes.

    Saturday, May 04, 2013 5:14 PM
  • Hi Piotr, 

    Is there a case where you can build programmatically the XMLA directly from the relational model? 

     

    Saturday, May 04, 2013 6:13 PM
  • Hi Piotr, 

    Is there a case where you can build programmatically the XMLA directly from the relational model? 

    Hi SpyrosSavvides,

    XML for Analysis (XMLA) is a SOAP-based XML protocol, designed specifically for universal data access to any standard multidimensional data source that can be accessed over an HTTP connection.

    For more information, please see:
    Developing with XMLA in Analysis Services: http://technet.microsoft.com/en-us/library/ms186654.aspx

    Regards,


    Elvis Long
    TechNet Community Support

    Wednesday, May 15, 2013 2:20 AM
    Moderator
  • An alternative can be to use the .QueryByCube() LINQ extension method provided by the AdaptiveLINQ component.

    Here, dimensions and measures are defined using C# expressions.

    .QueryByCube() acts as a "GroupBy + Aggregation" where grouping key selector are determined at runtime.

    To be clear, I said that I am the developer of AdaptiveLINQ.

    Saturday, February 08, 2014 10:01 PM