Author Archive

Power Query with SSIS OData Feed Publishing Components

November 6, 2014 Leave a comment

Power Query won’t be able to connect to Oracle / IBM DB2 database unless the driver is installed locally. In many cases, we cannot freely install the driver due to enterprise security policy and lots of other reasons. And to mass distribute the diver to client PCs is another big issue. SSIS Data Feed component removes this gap via exposing complex ETL as a single SQL Server database view which can be queried by Power Query directly. With SSISDB introduced in SSIS 2012, powerful auditing and performance logging can be enabled with the help of out of box reporting features as well as lots of nice CodePlex community projects.

In a recent project, I use this technic to enable a scenario that an end user is able to query a SQL Server database view which actually invoke an ETL job in real time and streaming data back to Excel. If you do have the Power BI subscription, you can even share the Power Query through Power Query Data Catalog and become searchable by colleagues. To take further, I build an ASP.NET as a query register and capture some metadata of the query. Then I use Varigence Mist (BIML IDE, a super powerful tool!) to dynamically generate the SSIS packages on the fly and deploy to server via Power Shell and create a view on top of it. It is truly awesome and I’m proud of what I achieved.

The only drawback is that the Project Deployment does not support incremental deployment at the moment. I wish in next SSIS release, the incremental deployment can be implemented so that we can add additional SSIS Data Feeds without redeploy the entire project.

Filtering and Custom Maps arrive to Power Map with September update

September 10, 2014 Leave a comment
Categories: BI, Power BI Tags: ,

FWD: ERP industry moving from project to process, but what about reporting?

September 4, 2014 Leave a comment

Is this what it will take to step up the game and satisfy the ever growing needs of information hungry end users?

via ERP industry moving from project to process, but what about reporting?.

Categories: BI Tags:

FWD:“I Modified an Existing Table in Power Query and Now it Won’t Refresh”– A Fix

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, SSRS 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:


Categories: BI, SQL Analysis Services Tags: ,

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:


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


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:

      'with member measures.x as 100 select {measures.x} on 0 from <Your Cube>'
      'SELECT * FROM OPENQUERY('+@CubeLocation+', '''+ @mdx +''')'      
EXEC sp_executesql @SQL 

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


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


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>”:

      '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 

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


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:


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:

SET @mdx=' <Your MDX longer than varchar(8000)> '
Exec (@mdx) At <Your Linked Server>;

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

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

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:

 SET myset AS
 <Your SET Definition>
 Member [Measures].[cnt] AS
 [Measures].[cnt] ON columns
FROM <Your Cube>
 <Your Slicers>

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