Post reply

How To Register & Run MDX Query Using CLR Programing in SSAS Server (Assemblies)

  • April 15, 2013 at 11:55 am

    #276498

    I Have A Simple MDX Query

    "Select [Measures].[Book Value GAAP USD] on COLUMNS From [Positions Cube]"

    I Need To Run THis Query Using CLR Program (C# Code) and Register It In SSAS Server I Mean In Assemblies

    I Tried SomeThing Like This .... Its Not Working (I am Pretty Much New To These Type Of Things)

    using System;

    using System.Collections.Generic;

    using System.Text;

    using System.Data.SqlClient;

    using Microsoft.AnalysisServices;

    using Microsoft.AnalysisServices.AdomdServer;

    namespace BI.MDX

    {

    public sealed class BI_MDX_PosTrans_SP

    {

    public static float GAAPBV(float bv)

    {

    AdomdCommand objCommand;

    string sConnString;

    string strCommand;

    AdomdConnection objConnection;

    strCommand = "Select [Measures].[Book Value GAAP USD] on COLUMNS From [Positions Cube]";

    sConnString = "Data Source=dev.bisqlas003.metlife.com;Initial Catalog=Inv_PosTrans";

    AdomdConnection

    objConnection.ConnectionString = sConnString;

    objConnection.Open()

    objCommand = new AdomdCommand(strCommand, objConnection);

    objCellSet = objCommand.ExecuteCellSet();

  • April 27, 2013 at 12:47 pm

    #1610616

    Usually you would not hard-code the text of a query into CLR like this. In Analysis Services, you can only create CLR stored procedures - unlike in relational databases, where you can do CLR data types, triggers, aggregate, etc. You would call an Analysis Services stored procedure much like a function that operates on a row returned by a query, such as SELECT Column1, Column2, MyStoredProcedure(Column3), etc.

    What you're trying do is much more like hard-coding the definition of a view, which isn't the usual use case. But if you really wanted to do that, you could use syntax like "CALL MyStoredProcedure." I was just writing a blog post the other day on how to do this with SQL Server Data Mining, but haven't posted it yet in my series at http://www.sqlservercentral.com/blogs/multidimensionalmayhem/2013/04/26/a-rickety-stairway-to-sql-server-data-mining-part-11-model-comparison-and-validation/. If you let me know the text of the errors you received, I might be able to help.

    -- Steve

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply