Antoon’s datawarehouse blog

I’ve updated the examples and templates for SQL Server 2008 R2, you can find it at the Codeplex.

I’ve asked Microsoft for a standard component to handle error logging, wonder how that will go

You can find it here , vote for it if you’re interessted.

My custom error log component has been used by CozyRoc to create there own version.

We still use the component in every package we make. It’s beyond me why Microsoft hasn’t created something like it. I wouldn’t know how to build (maintain) a compex package without it.

I’ve been thinking about a new version that would solve the problem that the component doesn’t always has all the information it needs to name the item that causes the error. And I would like to add information about why/where the error in an expression occurs…

I think this is actually possible, I found somthing somewhere on looping through all components of a package to get all the information regarding the components, expressions … but I can’t find it anymore … nore the time to make it

P a r t   4 :   T E M P L A T E   R E P O R TS

Find the complete project, examples and templates at the codeplex

Obviously you can’t go through the process of creating all these parameters and actions every time you want to make a “simple” drill- and filter- report.
So to cover most situations we use a template report that can quickly be adapted and hides most of the complexity we’ve covered in parts 1 to 3.

Read the rest of this entry »

P a r t   3 :  F I L T E R I N G   O N   M U L T I P L E   M E M B E R S 

Find the complete project, examples and templates at the codeplex

When analyzing reports we often want to compare the evolution of two or more products over time.  And perhaps even compare the internet sales of the first month of two different years across several products.

The filter we created in the previous report isn’t really suited for that purpose, because it won’t allow us to filter on several product-members (or date-members). So what we need is to be able to add several members to the filter before we actually start filtering.

Read the rest of this entry »

P a r t   2 :  P e r f o r m a n c e   a n d   f i l t e r i n g   o n   o n e   m e m b e r

 Find the complete project, examples and templates at the codeplex

 Regarding performance

Now, the performance off this report is pretty good. But, that has a lot to do with the limited number of members in our two hierarchies.

If, instead of a product hierarchy, we were to use a customer hierarchy with possibly hundreds of thousands of clients, things would slow down a bit.

Read the rest of this entry »

Abstract

In this article we will describe a technique to create Reporting services (SSRS) reports  that use Analysis services (SSAS) cubes as data sources, have a very intuitive interface and take full advantage of SSAS performance optimization features.
Basically the user-friendliness of the interface is achieved by allowing users  to drill up/down on the different hierarchies by simply clicking columns or rows, and applying filters by clicking on icons in rows or columns.

The performance is achieved by only retrieving the necessary data from the cube.

An image is better than a thousand words so let’s take a look at what we are trying to achieve:

 Drilling and filtering video

Article structure

The article in divided in four parts, adding functionality (and complexity) in the first three parts and providing some template reports in the last parts that hide most of the complexity.

• Part 1: simple drill trough
• Part 2: performance and filtering on one member
• Part 3: filtering on multiple members
• Part 4: template reports

There is an example report for each part, based on the Adventure works cube.

Find the complete project, examples and templates at the codeplex

Read the rest of this entry »

A while ago I wrote, a not to happy blog, about the (lack off) SSAS – SSRS integration.

Meanwhile I started using Excel 2007 and discoverd the BI possibilities. I must say, I’m impressed.
It’s supper fast (most of the time anyway) and very user friendly. BI for the masses, it’s true.

I’ve decided to switch all standard reports in out company from SSRS to excel and only keep SSRS reports for some very specific and complex reports.

Having said that, there are still some issues (hopefully solved in SS2008R2/Excel 2010, but I haven’t looked into that yet):

1. The report filters are just that: filters. They don’t slice the cube (as you would expect) but limit the results tothe selected member(s). F.e. if you have a time dimension on the columns and you select: 2009 and 2009Q1 you will get identical measures for both columns.
2. There are some security/user friendliness issues with the tool-tips displaying all member attributes. The attributes that haven’t been included in the perspective are also visible and even those that are hidden(!).
   I reported these issues to Microsoft (1) (2), no reply yet. 
3. The all-member (once again) has been left out. Heaven knows why SSRS and excel refuse to use this.
4. A filters can’t be shared between pivot tables. So if you want to create a report for a business unit with a number of pivot tables in it (with a filter for the business unit). They will have to adjust the filter for each pivot table.
5. Setting a filter on a dimension with a large number of members can take a long time and even fail.

Looking forward to the power pivot…

Found this incredible usefull post at: http://prologika.com/CS/forums/p/370/1564.aspx
to solve the problem of the & character that is used to denote member keys in SSAS