Archive

Posts Tagged ‘SSAS’

News On SSAS Data Source Support In Power BI

November 7, 2014 Leave a comment

Chris Webb's BI Blog

Yesterday we heard (again) that SSAS will be supported as a data source for cloud-based reports in Power BI. Today, in a session, two new important details on this emerged:

  • It will work with both Tabular and Multidimensional
  • It will connect as the user running the query, so SSAS security (eg dimension security) will work just the same as it does on-premises. No special setup will be needed; there were no details apart from the fact it will work using the EffectiveUserName connection string property.

I’m sure a lot of people will be interested to hear this…

View original post

Advertisements

Lazy-loading with MDX (MDX Pagination)

March 13, 2013 3 comments

In a conventional T-SQL query, pagination can be applied to retrieve a subset of the total result-set. In the web development, we can use this technique to achieve lazy loading. This post will discuss how to do pagination in MDX to achieve lazy-loading

The key is to use subset function in MDX. MSDN has documented the function here.

But before you use the subset function, you have to know the total number of the result set first to calculate the total page number. The below template illustrate how you get the row count:

Image

you can simply wrap the above MDX into a OpenQuery and assign to a variable.

Once you have the row count then you can decide how you control the pagination

Image

In a web page, the initial load is not so fast considering the row count is extra task. But once you go to page 2,3, etc, you’ll feel the performance is really fast.

Categories: BI, SQL Analysis Services Tags: ,

OpenQuery and Ole DB behaviour – MDX runs twice

March 13, 2013 4 comments

Issue: Linked Server + Ole DB provider to SSAS’s combo makes the MDX to be executed TWICE which means doubling the execution time.

Resolution: Unfortunately, there is no way around it. The limitation is Linked Server with Old DB provider. According to my finding, “openquery” theoretically performs better than “exec @query At <linked server>” but on the other hand, the latter supports string longer than varchar(8000) or nvarchar(4000).  Hence, maybe we can use openquery when the string length <= 4000 and use “exec @query At <linked server>” when string >  4000 (nvarchar)

Now I’ll walk through my findings:

Query using openquery:

DECLARE @CubeLocation AS nVARCHAR(100)='<YOUR LINKED SERVER>'
DECLARE @mdx AS NVARCHAR(4000)=
      'with member measures.x as 100 select {measures.x} on 0 from <Your Cube>'
DECLARE @SQL AS NVARCHAR(4000) = 
      'SELECT * FROM OPENQUERY('+@CubeLocation+', '''+ @mdx +''')'      
EXEC sp_executesql @SQL 
Go

Run the above query first time, I get the following:

Image

Run the above query 2nd time, I get the following:

Image

Conclusion: For any new query, it is going to be executed TWICE, the subsequent execution will be run once. However, if you change the query string, it will be executed TWICE again. For example, I add an space before “with” or after “member” or anywhere in the query.

Query using “exec @query At <linked server>”:

DECLARE @CubeLocation AS nVARCHAR(100)='<YOUR LINKED SERVER>'
DECLARE @mdx AS NVARCHAR(4000)=
      'with member measures.x as 1 select {measures.x} on 0 from <Your Cube>' 
DECLARE @SQL AS NVARCHAR(4000) = 'Exec (''' + @MDX + ''') At ' + @CubeLocation            
EXEC sp_executesql @SQL 
GO

Run the above query  first time, I get the following:

Image

Run the query 2nd time and I get the same behaviour: it is executed TWICE.

Conclusion: Regardless how many time the same query runs, it is always executed TWICE.

For a tiny MDX query, you probably won’t be able to see the time gap between two MDX query as it is executed within milliseconds. But for a long running MDX queries, you will notice there will be non-overlap time gap between the execution duration. I’ve tested a MDX query which runs about 53 seconds natively on SSAS. And If I run it through openquery, you’ll see the below:

Image

As you can see, the query is executed TWICE and the total duration is 54143ms+44697ms = 1.647 minutes. However, the dataset is about 3298 rows and 700+ columns which took about 3 minutes to render in SQL management studio. And I can verify that even SQL Profiler reported the “query end” event, the SSMS is still busy with rendering the result set in the data grid. Also, I verified that if using client tools which support direct SSAS connectivity, e.g. Excel native PivotTable or ADO.NET provider, the query is executed ONCE only.

Overcome varchar(8000) or nvarchar(4000) limit in OpenQuery to SSAS

March 13, 2013 5 comments

OpenQuery has query string length limit to varchar(8000) or nvarchar(4000)

To overcome this limit you can use the below query:

DECLARE @mdx AS VARCHAR(MAX)
SET @mdx=' <Your MDX longer than varchar(8000)> '
SELECT len(@mdx) AS LENGTH_OF_MDX
Exec (@mdx) At <Your Linked Server>;

to use dynamic SQL, you can use the below tempalte:

BEGIN TRY
	DECLARE @SQL nvarchar(max)
	SET @SQL = 'Exec (''' + @MDX + ''') At ' + @CubeLocation
	EXEC sp_executesql @SQL
END TRY
BEGIN CATCH
	SET @ErrorMessage = 'Query execution failed!'
	RAISERROR(@ErrorMessage , 16, 1) WITH NOWAIT
END CATCH

However, although the above can bypass the varchar(8000) limit, it brings another side issue. If you MDX returns a empty set, the SQL Server connection will be dropped. And even worse, this error cannot be captured by Try Catch block. Hence, if you need to implement this logic in your SQL stored procedure, you need to implemented MDX Count function to avoid returning no result set. For instance, count the result-set first and if count = 0, there will be no need to go ahead with the query. Here is the code sample for a function to return the row count of your MDX result set:

</pre>
WITH
 SET myset AS
 <Your SET Definition>
 Member [Measures].[cnt] AS
 myset.count
SELECT
 [Measures].[cnt] ON columns
FROM <Your Cube>
WHERE
 (
 <Your Slicers>
 )
<pre>

You can then wrap the above query in dynamic SQL which uses OpenQuery and linked server.

Top N records with OpenQuery against SSAS data sources

March 13, 2013 Leave a comment

OpenQuery + Linked Server is commonly used if you want to querying against SSAS cube through T-SQL. However, you cannot use “top N” to specify how many records you want to retrieve. try SELECT top 1 1 AS id, * FROM OPENQUERY(<Linked Server>,<Your MDX Query>) yourself and you’ll find all records are returned, which means the top 1 is ignored.

However, if you try the following:

Select *
from (	Select top 1000 ROW_NUMBER() over(order by id) as RowNum, t.*
		from (	SELECT top 1 1 AS id, * 
				FROM OPENQUERY(<Linked Server>,<Your MDX Query>)) as t
      ) as ds

You’ll get top 1000 rows if the whole result set contains more.

Now to achieve fake (what I mean by fake is that it is not true pagination at SSAS side) pagination, you do sth like:

DECLARE @start AS INT
		,@end AS INT
set @start = 1
set @end = 20

Select *
from (	Select top 1000
			ROW_NUMBER() over(order by id) as RowNum, t.*
		from (	SELECT top 1 1 AS id, * 
				FROM OPENQUERY(<Linked Server>,<Your MDX Query>)) as t
     ) as ds
where ds.RowNum between @start and @end
Go
Categories: BI, SQL Analysis Services Tags: ,