Filtering and Custom Maps arrive to Power Map with September update

September 10, 2014 Leave a comment
Categories: BI

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

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

Categories: BI

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

Get every new post delivered to your Inbox.

Join 43 other followers