How to Design SSRS Multi-Pie Chart

March 19, 2013 Leave a comment

Multi-Pie Chart in SSRS

Multi-Pie Chart in SSRS

The above is the output of my SSRS report. There is no built-in multi-pie chart in SSRS but it is very easy to design your own.

Multi-Pie Chart Design in SSRS

Multi-Pie Chart Design in SSRS

Basically, you place 4 table controls in the layout design and make sure all 4 are placed in a single rectangle control to make sure the vertical align is OK. All 4 table controls are sharing the same data set. In my case, it is “FY” which returns a single column table with values as 2001-2002, 2002-2003, 2003-2004 etc.And each table control contains a sub report which renders a pie chart. The trick is that I config the “Filters” in the table, shown as below:

SSRS_Multi-Pie Chart_Expression

As you can see, the modulo arithmetic operator enables splitting your data set into 4 sub sets. The 2nd table will have the filter with Value as 2 and 3rd table has value 3 and 4th table has value 4.

I did a quick and dirty way to quickly set it up. In reality, you probably want to check the first data member in your date set as they will impact the order and position of your pie chart. You could wrap the expression with addition IF / Case statement to check the first data member and decide the remainder accordingly. And if your data member does not have numeric values, you can always use row_number() over() to get an ordered numeric value.

 

 

Categories: BI Tags: ,

FW: Slides from SSIS Performance Design Patterns | TechDays Hong Kong 2013

March 15, 2013 1 comment
Categories: BI, SQL 2012, SQL Integration Services Tags: ,

Write your SQL or MDX code on your WordPress blog

March 13, 2013 Leave a comment

If you need to embed the SQL or MDX source code, use the below template:

1

Categories: BI Tags: ,

Lazy-loading with MDX (MDX Pagination)

March 13, 2013 Leave a comment

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 6 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 4 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: ,
Follow

Get every new post delivered to your Inbox.