BI review & help

My friends were stuck with a totally weird bug this week. After a day of frustration they called me for the rescue. It took me some time to figure it out and I think that every SSIS developer (and maybe every developer) can learn a thing or two from others' mistakes.

The mission: The data flow takes one table with duplicate rows and copies it to another table and makes sure that every row will appear only once. In the way, the data flow also adds some irrelevant fields. Among them is the Create_User and Create_Date fields which tells by who and when the package last ran.
How my friends did it: Again, it's a very simple flow. They only added Derived Column transformation to add the new fields and then they added an Aggregate transformation to make every row appear only once.

Note that this is not the real package. It's a sample I did on my machine to show it here.

The Bug: When I first seen this it seemed to me very simple flow and I asked myself how can it be that this is happening:

As you can see, it seems that the Aggregate transformation is not deterministic. Sometimes it outputs 99 rows, sometimes 198 and in some other times I get other results as well.
Investigating: I wanted to see what's the difference between the table that I got in the first time (99 rows) and the table I got in the second time (198 rows) so I changed the destination table and compared the two tables. I ran "select * from A where Column1+Column2+... not in (select Column1+Column2+... from B)"-style query but it was no use - it showed me that there were no rows that appeared only in one of the tables. In this step I really started to think (as my friends did) that maybe the Aggregate transformation has something wrong inside... Instead of blaming Microsoft, I decided to think. I needed to see what can make the flow non-deterministic. Then, it hit me.


The only non-deterministic component in the flow is the Derived Column because it has the getdate() function (it may be simple to see here, but in the original package the derived column transformation had many fields). The results of this function may differ in the milliseconds, especially for large tables. Then I looked in the Aggregate transformation and seen that the Create_Date column also was in the Group by operation, meaning that if two rows has different millisecond they will be placed twice in the destination table, although they are the same in every column. That's it, the bug was found. But still, one question remained: Why the query did not show me this? The answer is also simple but tricky to find: In the comparison query I concatenated all the columns in the tables in order to compare the results. When I did this, I casted the Create_Date to nvarchar which truncated the milliseconds.

Conclusions:

• Pay attention to non-deterministic elements in what you do, whether it's code or ETL process.
• When you do dummy stuff like checking all the checkboxes in a list - think what are the outcomes.
• Call Miky when you're desperate.

This week I had something disturbing. When I installed Excel 2003 on the Panorama machine in order to use Excel functions in my MDX calculations, the NovaView Desktop stopped working. When I tried to load a view it threw an error in connection message. Calling to Panorama support, they told me that it's a known issue and it's hard to find by using the Panorama knowledge base. So here it is:

If you have connection issues in the Desktop program, enter the registry editor (Start -> Run -> regedit). Look for HKEY_CLASSES_ROOT\MSOLAP\CLSID and make sure it's the same as HKEY_CLASSES_ROOT\MSOLAP.3\CLSID. Remember - always copy from MSOLAP.3 to MSOLAP and not vise versa.

My blog was down for couple of hours because of this bug which been fixed in the later versions of dasBlog. I haven't found the solution in google search so this post is for those who will search it in the future (google scans every post in this blog). The solution(s) can be found dasBlog/Thread/View.aspx?ThreadId=34910&ANCHOR#Post115981">here.

In the last years I've seen many astonishing BI web sites. I always asked myself what I need to do to bring my customers such beautiful web-based BI solutions. After having much experience with Panorama NovaView and especially the Panorama SDK I started to run some questions in my mind: Why won't I build some re-usable puzzle pieces that can be joined together to a web site? These pieces can be web controls that using and even interacting Panorama views and Analysis services. Why won't publish it as open source and give it to the BI community?

The PanoramaBasedWebSite project is a toolkit that contains web controls you can easily use in your ASP.NET based web site. The project is written in ASP.NET 2.0 and C# 3.5. These web controls interacts with Panorama views (using Panorama SDK) and Analysis Services (using AMO).
The idea is that you can take these puzzle pieces, combine them as you like in your web site and create your good-looking BI web site with almost no programming. The project is only in its first steps, but I believe that publishing the design/idea is also important. This is why the first release is already published, although it has only two web controls so far. This is what we have so far and what I'm planning for the future. I'll be happy to hear your thoughts/ideas:

First Release Contents

• PanoramaView web control - this is the main control of the project and it will probably take a lot of the project's weight. The control simply shows panorama view. For now, it doesn't do much rather then showing a view so there's a lot of work to do for this control. It gets two properties - BriefingBookName and ViewName. You can look at the TODO: comments in the code to see what future plans I have for this control.
• UpdateDatePanel web control - this control shows the date and time when the last process of the cube was made. It can be used in two ways: You can only set the PanoramaViewID property. The control will extract the cube and the database name from the view and take the update date from the cube. The other way is to set the CubeName and DataBaseName properties.

Future Plans

• KPIView - Already working on it. Similar to PanoramaView, but if the view shows KPI then a drilldown will be made when the user clicks on a gauge.
• QueryList - Shows the result of MDX query. For example, the list shows the top 10 employees of the month (in sales perspective, for example). This list will be interactive, meaning that clicking on a row will make a drilldown, drill to data or replace the list with another query results.
• DimensionPicker - Gives the user the ability to pick members of a dimension/hierarchy. After selecting, the control will slice all the views on the page (or only predefined set of views).
• DatePicker - Same as DimensionPicker but for dates. It will show a calendar to the user and clicking on a date will perform a slice in the views.

The use of the controls in your aspx pages is very easy. You can see for yourself:

<PanoramaControls:UpdateDateLabel ID="UpdateDateLabel1" runat="server" PanoramaViewID="PanoramaView1" />
<PanoramaControls:PanoramaView ID="PanoramaView1" runat="server" Width="100%" Height="80%" BriefingBookName="MikysBook" ViewName="MyFirstView" />

I'll be happy to read your thoughts and ideas about this project. There will be more to come. Stay Tuned.