Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How To Register & Run MDX Query Using CLR Programing in SSAS Server (Assemblies) Expand / Collapse
Author
Message
Posted Monday, April 15, 2013 11:55 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, September 21, 2013 4:36 PM
Points: 12, Visits: 102
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();

Post #1442433
Posted Saturday, April 27, 2013 12:47 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 03, 2014 8:46 PM
Points: 3, Visits: 173
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
Post #1447288
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse