Executing MDX StoredProcedures using AdomdClient.dll

Hi fellow developers,

At the moment, i have a SSAS 2005 installed and a working cube on it. Everything is working fine. However i want to have an ASP.NET Page to access some of the data in the cube and present it.

Well this would be no problem at all, if i use the AdomdClient and send queries over it, but this destroy my layer architecture. I dont want any Queries in my Application, but in the SQL Server.

When i used a sql server database i always used stored procedures to access the data because of security and consistence.

Now is there a possible way to create MDX Stored Procedures and execute them with the Adomd API like i did with sql server databases?

Or other clean solutions like creating mdx libraries on server and access it with the client?

I appreciate your help.

Sincerely

David

PS: If you find any gramatically mistakes, keep em :-))

Moving to SQL Server Analysis Services forum.

Thanks,
Sarah

|||So far there is no support for stored procedures. There is support for parameterized queries though.

So you do not want ASP.NET code contain explicit text of MDX queries, right? What is your real goal here? Do you want to hide the text of the MDX queries from the writer of ASP.NET code?

How about creating some .NET component accepting a reference to the connection object, a name of the "stored procedure" and list of parameters? The component would fetch the real text from some other store, use the passed connection and parameters and pass the query to the server. Then return the results back to the client.

This is really not that useful, but maybe it will cover your goal.|||

Hi Andrew

Thanks for your answer.

Well the goal would be that, other applications could access the same Queries, without copying the Queries in their application code. However we decided in the meanwhile that we use the queries in the code, because we dont have any time to search other clean solutions :-)

But still i am interested in a clean splitted Query Logic -> Application Logic solution. If anyone knows? Please let me know.

Sincerelly

David

|||By other applications you probably mean those also developed by you, right? The applications could load the component. It could also accept references to a store of MDX queries so that different scenarios would load different stores and keep the queries in memory (so that not to have performance problems in multithreaded scenarios).sql

Executing MDX queries from inside SQL Server Stored Procedures (SQL Server 2005)

Hello all,

Does anyone have any idea how to access a cube from stored procedures in SQL Server 2005?

My idea was to use SQLCLR and write a function in .NET that accessed the cube through ADOMD, but there are problems with that.

See the following code sample:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using Microsoft.AnalysisServices.AdomdClient;

public partial class UserDefinedFunctions
{

[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString MDXRdr()
{
AdomdConnection conn = new AdomdConnection();
conn.ConnectionString = @."Provider=SQLNCLI.1;Data Source=JOAHSE0\SQL2005;Integrated Security=SSPI;Initial Catalog=dbRenTAK";

conn.Open();
// Just output cube name
string str = conn.Cubes[0].Name;
conn.Close();

return new SqlString(str);
}

};

First, I wasn't able to add a reference to AdomdClient from Visual Studio. I then did add a reference manually in the project file and it compiles. But when I try to deploy, I get an error message that the "assembly adomdclient was not found in the SQL catalog".

Thanks in advance for any suggestions or hints!

Best Regards,

Johan ?hln
Consultant, IFS

Moving to the "Data Mining" Forum, which is better suited for this question.

executing arbitrary statements in mdx

for example if I want to get a feel for how a particular function operates, can I do something like this ...

select {cdate("1/1/2005")} on columns

from [itdev1 hk]

what would be the correct syntax?

Hello! My recommendation is to start with simple MDX expression like calculated members.

Here is a good site wih a lot of MDX tutorials for beginners and advanced users:

http://www.databasejournal.com/features/article.php/3593466

Look for William.E.Pearson

HTH

Thomas Ivarsson

|||It usually looks something like this:

Code Snippet

WITH MEMBER [Measures].[TestMeasure] AS 'cdate("1/1/2005")'
SELECT {[Measures].[TestMeasure]} ON COLUMNS

I can't remember if you need a FROM when you aren't referring to any real dimensions/measures. If it complains, just point it at a cube.
|||

note: i did need to put in a cube name, and after that it worked great.

nice one!

|||etetetet

executing arbitrary statements in mdx

for example if I want to get a feel for how a particular function operates, can I do something like this ...

select {cdate("1/1/2005")} on columns

from [itdev1 hk]

what would be the correct syntax?

Hello! My recommendation is to start with simple MDX expression like calculated members.

Here is a good site wih a lot of MDX tutorials for beginners and advanced users:

http://www.databasejournal.com/features/article.php/3593466

Look for William.E.Pearson

HTH

Thomas Ivarsson

|||It usually looks something like this:

Code Snippet

WITH MEMBER [Measures].[TestMeasure] AS 'cdate("1/1/2005")'
SELECT {[Measures].[TestMeasure]} ON COLUMNS

I can't remember if you need a FROM when you aren't referring to any real dimensions/measures. If it complains, just point it at a cube.
|||

note: i did need to put in a cube name, and after that it worked great.

nice one!

executing analysis services query via openrowset

Can you kindly tell me what settings are required to execute an MDX statement via openrowset.

Currently I am executing it by impersonating my user (sql user) as "sa" account and everything goes ok.

The following is the query i am using

SELECT "[Dim Agent].[Dim Agent].[Dim Agent].[MEMBER_CAPTION]" AS AgentNumber,

"[Dim Application].[Dim Application].[Dim Application].[MEMBER_CAPTION]" AS ApplicationId,

ISNULL("[Dim Event].[Dim Event].&[1]",0) AS PropertyViews,

ISNULL("[Dim Event].[Dim Event].&[2]",0) AS ScheduleAShowing,

ISNULL("[Dim Event].[Dim Event].&[3]",0) AS ContactMe

FROM OpenRowset('MSOLAP.3',

'DATASOURCE=RIGGINS2\LFDB2; Initial Catalog=PicassoLnfWebMetric;Integrated Security=SSPI',

'SELECT {[Dim Event].[Dim Event].&[1],[Dim Event].[Dim Event].&[2],[Dim Event].[Dim Event].&[3]} ON COLUMNS,

NON EMPTY([Dim Agent].[Dim Agent].[Dim Agent] * [Dim Application].[Dim Application].[Dim Application]) ON ROWS

FROM [Lnf Web Metric] WHERE {([Dim Date].[Date].&[2007-05-20T00:00:00]:[Dim Date].[Date].&[2007-05-28T00:00:00],[Dim Agent].[Agent Status].&), ([Dim Date].[Date].&[2007-05-20T00:00:00]:[Dim Date].[Date].&[2007-05-28T00:00:00],[Dim Agent].[Agent Status].[All].UNKNOWNMEMBER)}')

Can you kindly let me know what do i need to do to run this query by impersonating as some windows account?

Warm regards,

Sudhir

I don't think you can do this using OpenRowset(). You could try setting up a linked server and then using OpenQuery(). There are options when you set up a linked server that let you specify a security context. If your SQL and AS services are on the same machine you should be able to get this working, if they are on separate machines you would need to configure Kerberos authentication. (There are various whitepapers available on how to do this)|||can you redirect me to some whitepapers?|||

On configuring Kerberos? sure http://support.microsoft.com/kb/917409 & http://sqljunkies.com/WebLog/mosha/archive/2005/01/25/6905.aspx - specifically relates to AS2005

On adding a linked server http://msdn2.microsoft.com/en-us/library/aa936675(SQL.80).aspx, you also have to make sure with AS that the provider is set to run In-process.

Execute MDX from SQL Proc

------------------------

Can someone shed some light? I have searched hi and low for an answer to my dilemma. I have an mdx query that works fine in my olap environment but when I try to run the same query from sql 2000, one of my calculations comes back as a long int and includes (E-02) at the end. Can you please review my store procedure and provide some insight?

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER PROCEDURE GetWeeklyTrendData

@.Level int,
@.year int,
@.week int,
@.PrintMDX int = 0

AS

SET NOCOUNT ON

DECLARE @.MDX varchar(4000),
@.CubeQuery varchar(4500), @.Leaf int, @.Tier int

BEGIN
SET @.MDX = '''
WITH
MEMBER [Measures].[OrgName] As ''''[GL].CurrentMember.Name''''
MEMBER [Measures].[LevelSKey] As ''''[GL].CurrentMember.Properties("Key")''''
MEMBER [Measures].[OrgType] As ''''Iif(IsLeaf([GL].CurrentMember), 1, 0)''''
MEMBER Measures.Lookp AS '''' LookupCube("cubeName", "(Measures.Customers, " + [GL].CurrentMember.UniqueName + "," + [Week].CurrentMember.UniqueName + ")" )''''

MEMBER Measures.Trend AS '''' Measures.Customers / LookupCube("cubeName", "(Measures.Customers, " + [GL].CurrentMember.UniqueName + "," + [Week].CurrentMember.UniqueName + ")" )''''
, SOLVE_ORDER = 1, FORMAT_STRING = ''''Percent''''

SELECT
{
CrossJoin
(
{ [Week].[' + CAST(@.year AS VARCHAR) + '].[Week ' + CAST(@.week AS VARCHAR)+ '].Lag(12) : [Week].[' + CAST(@.year AS VARCHAR) + '].[Week ' + CAST(@.week AS VARCHAR) + '] },
{ Measures.OrgName, Measures.LevelSKey, Measures.OrgType, Measures.Customers, Measures.Lookp , Measures.Trend
}
) } ON COLUMNS,
NON EMPTY
{ [GL].&[' + CAST (@.Level AS VARCHAR) + '].Children } ON Rows
FROM [cubeName]
WHERE

( [Monthly Income].[All Monthly Income].[Y],[Contact Events].[All Contact Events].[Y],
[Other Financial].[All Other Financial].[Y],[Bankers Notes].[All Bankers Notes].[Y],
[Employer Name].[All Employer Name].[Y] )
'''
END
SELECT @.CubeQuery =
'
SELECT *
FROM OPENQUERY(ReportDBMart, ' + @.MDX + ')'
IF @.PrintMDX = 1
PRINT 'MDX: ' + @.MDX

EXEC(@.CubeQuery)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

The following is the value that I get back.
9.4993517502742597E-2 and is derived from Measures.Trend.

My guess is that it doesnt recognize FORMAT_STRING = ''''Percent'''' or its trying to return a numeric value inside a varchar. Any thoughts?You've got a real number expressed in scientific notation. The 9.4993517502742597E-2 is exactly the same thing as 0.094993517502742597

-PatP