Claus on Code

After spending time debugging my analysis services cube for the second time having this error, I guess it will be a good idea to blog about it, so I can remember it to another time

I got the above error after I had modified a dimension, so used it three fields for the key, instead of one. I made the relationship from my facts, based on the 3 keys, and then I deployed and processed the cube. And then I got the above error.

The reason for the error is, that I had the partitions for the fact table, based on some SQL sentences, and I had forgotten to modify these partitions after I had made the new relation.

So the conclusion: When you get the error “Invalid column name ‘x’.; 42S22″ in analysis services, it is a problem related to the partitions design.

I just had a SSIS package that had been running every day for the last couple of years without any problems, which failed on me with the following error message:

Warning: Null value is eliminated by an aggregate or other SET operation

The SQL source had a group by, where I was taking a minimum on some datetime fields. I needed it to return NULL values because I had some additional logic that took these NULL values into account.

What was even stranger was, that when I ran the package by double clicking it on the server, it completed with success.

So apparently a warning from the SQL server causes an error in SSIS, but only sometimes….. Nice….

The solution is to either change your SQL, so you don’t get NULL values, by for instance use COALESCE or ISNULL, or if you can’t do that, put this statement in front of your SQL : SET ANSI_WARNINGS OFF

Read more about the problem/bug here:
https://connect.microsoft.com/SQLServer/feedback/details/483175/failure-because-of-warning-about-null-aggregation

Yet another irritating error message from reporting services

You will typically get that error, when you try to create a calculated member in the visual designer like so:

IIF( [Measures].[x]=0 , [Measures].[y] , 0,00)

And then use the SUM or another aggregate function on the new measure.

If you create the same calculated member in pure MDX, and run it in for instance management studio, it will not yield any errors. Like so:

WITH MEMBER [Measures].[z] AS
IIF( [Measures].[x]=0 ,
[Measures].[y] , 0,00)

Just to describe the calculated member in words. If the measure x equals 0, then the new measure z will be the value of the measure y, and if the measure x not equals 0, then the new measure z will take the value of 0,00.
The problem is that, when reporting services SUM the new measure z, it Sums data from y and 0,00, and reporting services will in some cases see the values as different data types. In my example the measure x was a decimal, and reporting services interpreted the 0,00 as an integer.

To avoid this problem, you have to tell reporting services that 0,00 is a decimal like this:

IIF( [Measures].[x]=0 , [Measures].[y] ,  Cdec(  0,00))

In regards to my earlier link to the power pivot blog post, that compared analysis services to power pivot, and discussed when to use what, I also have to throw in a link that compares all the front end BI tools in sharepoint 2010, and discuss when to use what there.

You can find a great overview of the frontend tools here:

http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=fc97d587-ffa4-4b43-b77d-958f3f8a87b9#filelist

There are a lot of possibilities, and my short advice is: Don’t try to use them all at once, even though you think you have a big project, which needs it all. Start by identifying what tool you need the most. You will properly be able to create most of what you need with that. Don’t throw a lot of different reports after the users. They will be confused when they get more advanced drill through possibilities in performance point services, than they for instance do in an excel services report, and they will get confused when they can’t export their performance point services report to PDF, the same way they can with for instance reporting services.

We are in a period in time where MS soon will be releasing a lot of (new) BI technologies, such as sharepoint 2010 which contains among other things: excel services, performance point services and hosting of powerpivot. I guess office 2010 will be released the same date as sharepoint 2010, and here we also have a lot of new stuff, especially in Excel (where we also have the power pivot plug-in). SQL server 2008 R2 will also soon be released.

Working as a consultant I have experienced some confusion about when to use what technology, both from customers and BI developers.

To clarify some of it, the powerpivot team has created a great blog post that compares analysis services to powerpivot, and discusses when to use what.

Read it here: http://blogs.msdn.com/powerpivot/archive/2010/03/12/comparing-analysis-services-and-powerpivot.aspx

Enjoy

This is the first part of Introduction to BI.

Before I start with the actual Introduction to BI, I would like to start with an introduction of myself, to clarify what I will be focusing on in this article series.

The last couple of years I have been working with BI on the MS platform, spanning from analyzing source system, over ETL design with SSIS, designing Enterprise data warehouses and data marts, designing SQL Server analysis services cubes to developing frontends with various frontend tools.

Before I entered the world of BI, I started as software consultant developing distributed enterprise applications typically handling input from a massive amount of users and/or devices. It was the designing of the software, so that it would react in with the same speed, no matter if 1 or 1000 users accessed it on the same time that triggered me. 

I soon learned that the information created by the users was of great importance; not only to the users using the system, but also to their managers and leaders, and that we not always could provide the desired data to them. We could create hardcoded reports of the data in our software, to show the data, but there was a lot of thing we couldn’t do, like:

• Let the user explore the data to try to find information of great importance, like comparing data from a single day, a single region, a single product, with the same thing from another region or time.
• Using data from different system in the reports.
• Keeping another security model in the reports than in than in the OLTP systems.
• Keeping the responsiveness of the reports fast when the data grew to millions of records.
• Handling history.
• Displaying trends in the data to the users.

And I could go on about with things we couldn’t do, but I hope some of it will become clear in this article series, where I will try to explain what BI is from a technical point of view, and how a BI solution is designed.  It was the desire to handle this information as an asset, which drove me towards working with BI.

The title of the article series is: Introduction to Business Intelligence from a developer point of view. With developer I mean both a software developer, that designs traditional OLTP systems and wants to learn more about BI, and a BI developer, who might want some insight/inspiration/input about how to design a complete BI solution. 

So what articles will this article series contain, and what will they be about:

Part 0: Introduction - Introduction to the article series (Yes, you are reading it right now)
Part 1: Defining Business Intelligence - In this article I will define precisely what Business Intelligence is, or at least what I mean by it.
Part 2: The Enterprise Data ware house - Here I will discuss what an Enterprise Data ware house is and why or why not you will need a DWH to your BI solution. I will also describe different modeling techniques.
Part 3: The Datamart -Here datamarts will be explained, and here I will also explain different modeling techniques.
Part 4: Frontend tools – The current state of frontend tools to BI.
Part 5: BI 2.0 – What is BI 2.0, and is the EDW dead?

Well, that is at least what I have planned for now.  There could come more, such as for instance about handling history, different data mining techniques and maybe about the MS toolbox to building data warehouses.

I will try to keep the articles short, precise and easy and fast to read. In my every day work, I work with MS tools, so the articles will be colored by that.

That is it for now.

Acronym list
BI: Business intelligence
EDW: Enterprise data ware house
MS: Microsoft.
SSIS: SQL server integration services.
SSAS: SQL server analysis services.
ETL: Extract Transform Load.
OLTP Systems: Online transaction processing systems.

Try it here:
http://www.microsoft.com/2010/en/
 
For more info and guides, read the article on the MS SharePoint Team Blog:
http://blogs.msdn.com/sharepoint/archive/2009/11/18/sharepoint-2010-public-beta-is-now-available-for-download.aspx

Just returned from the sharepoint 2009 conference, I’m really excited about all the new BI stuff.

One of the things I’m really excited about is powerpivot. As you might have guessed from the title, I’m going to elaborate on why I don’t think powerpivot will be a success, in the sense I don’t think it will fit for massive rollout to big organizations. I hate to that, before I have described what it is, and what I think is great about it(a lot of other people are doing that though), but here it comes:

There’s a lot of love regarding powerpivot in the MS BI and sharepoint communities around the world.
Just to challenge that a little bit

Sure, it is nice that you can pull more than a 100 million rows from various source systems like a SQL server, cubes, flat files, a data set from a reporting services report, a table in an HTML page, a RSS feed, or even from another powerpivot, relate these data to each other, and do advanced analyzing with a performance matching  the performance you would normally get from a cube running on analysis services. Well, it is more than nice. It is amazing in a crazy kind of way, but….

1)  More than one version of the truth: If powerpivot becomes a success, and a lot of people are publishing powerpivots to different places on the intranet, making new calculations based on data from the relational DWH, cubes, calculations from other powerpivots, calculations made in different reporting services reports etc. This is obviously going to result in having much more than one version of the truth.

And what about the scenario, if a person copies the powerpivot, from one place to another(The security is based on sharepoint, so he might have to do that, so another group can see it), and then make a change to one of the versions, but forget the other.

2) Performance 1: The reason powerpivot can do advanced analytic on your data with so good performance, is that it holds all the data in memory (The example from MS is with 100+ Million rows, and the size of the data was around 100-200MB.). That is nice on your local PC, when you are handling 1 powerpivot.

When you want to share it, you have to publish it to the sharepoint server. If powerpivot becomes a success, many people will do that. Then the server has to hold many powerpivot in memory, thus infuriating a lot of IT administrators.  

3) Performance 2:  When you publish your powerpivot to sharepoint, your data is not going to get updated . Everyone can click refresh data on the powerpivot, resulting in the powerpivot getting all  the 100+ millions row from the different source systems, linking the data together, recalculate all the calculations etc, but again, I don’t think  that is the way to make friends with the IT department. I guess there will be some way to set this up as a schedule in sharepoint, but again, if powerpivot becomes a success, there will be a lot of powerpivots in sharepoint, the IT department maybe don’t know from which source systems the powerpivots are pulling data, and many of the powerpivots will pull data from the same source.

4) Usability:  From the demos I have seen, I still think powerpivot is to difficult to use for the business users. I don’t want to elaborate on that, since this poat all ready is way to long.

I feel like I could go on about things I’m worried about regarding powerpivot, but I will stop now.

All this being said, I still think that powerpivot is a fantastic tool, but I think it is a bad idea to role it out for adhoc use to the average business users.

I’m currently in Las Vegas to the sharepoint 2009 conference. I’m hoping to get some time one of the days, to describe some of the new features in sharepoint.

I must admit I’m very excited, or as Steve Ballmer would say “pumped with energy”,  about the new things in sharepoint 2010 and office 2010 regarding BI and related technologies. 

If you are interested, go take a look at the conference homepage, there are already some videos online showing some of the new features.

I just created a new website: http://www.hugmun.org. In short, this is a website, that gather information about video and audio from around the web, and display it in a nice and easy way for the user. I wont say much more about it, since the webpage hopefully is self explaining

My goal with the webpage, was primarly to test different technologies, and I’m sorry to say, that non of them had anything to do with usability.
The technologies I have used:

• Linq to SQL,
• Linq to XML
• Calling .NET code from integration services
• Experimenting with different web crawler techniques

I hope you like the page