BI review & help

The query string was not meant to pass descriptions and string. The correct use of it is using codes and numbers. Anyway, my friend did write query strings with hebrew descriptions and didn't understand why the server side got it scrambled.

This was caused because the client side and the server side were using different encodings. The correct way of doing this is wrapping the strings using the JavaScript command encodeURIComponent (in the client side). This will turn the strings into Unicode. In the server side, using ASP.NET, you can use the Server.UrlDecode method to get it back.

After announcing the MdxInjection program I got several requests for additional details and for the ability to run it without using Visual Studio. So, here are some important points:

• When I published it I had developers in mind because I'm sure than anyone will want to do his little modification before using it for his own needs. That's why I published it as a solution and not as executable.
• I written it down using VS2008 but only with the .Net 2 framework. Those of you who uses VS2005 won't be able to open the solution.
• The program has only one public method - InjectMdx, who takes two arguments: The location of the CommonMdx file and the location of the xml configuration file.
• The CommonMdx.mdx file contains the common MDX script. The relevant part has to start with /* Common MDX */ and then the common mdx script. Anything written before it won't be treated. That gives you the ability to save some data or comments for yourself in this file.
• Example of the configuration xml file can be found in the Test libary inside the solution. Basically, it enable you to define in which servers, databases and cubes you want to inject the common script. Pay attention that you have to write the connection strings in this file.
• Note that the program will detect cube dimensions with their name changed and will know how to replace them. That means that if you mention the Time dimension in the common script and inject it to AdventureWorks cube, the script will replace the string "Time" with the "ShipmentDate" string, for example.

For those of you who want simple execution file, I added a windows console project in the solution.

Link to only executable program
Link to the solution with the added windows application project
Link to the solution without the windows application project

In the previous post I talked about the DRY principle in the BI Development. I mentioned that one of the major problems in the principle's implementation is in the common MDX code. Chris Commented:

"I'd like to be able to have a global MDX Script and be able to do something like a #include to bring calculations into specific cubes. One to add to my wishlist for the next version..."

And as I said there that I have a temporary good solution until we'll have it in the next SQL Server release (if someone from Microsoft is reading...).

The MdxInjection program takes your common MDX Script and a very simple xml file that defines where to inject this script. It injects the script into your desired cubes and even replaces the dimensions' names where necessary (it is relevant where you put dimension in a cube with a different name to thr dimension or when you use Role Playing Dimensions). I couldn't hold myself from writing some test code so it's also included in the project. The project is written in C# 2 using much AMO code. All the technical little details are inside.

Enjoy.

Download Link

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.

My blog is not an official .Net blog, but I find myself writing a lot of C# & VB.Net code over the last weeks.

I remember myself working a lot with ObjectDataSource and DataGrid/DataView/Repeater controls in order to reflect the user what is going on in my DB. The recent changes in the .Net world reflect the major demand from the developers to help us making this easier. First, we were introduced to LINQ which is the first level. Now, I believe that the ASP.Net Dynamic Data is the second level which brings it all together to the web environment.
We are all busy men and we often don't have much time to persue after all the new .Net frameworks and developments. My only way to stay tuned to what's going on is blogs, so I read when I have time. But when I want to learn it in more intimate way I watch screencasts.
I recommend all of you viewing the 17-minutes-cast from David Ebbo about Dynamic Data. It will show you what it's all about and after it you'll even be able to create it for yourself. I hope that this is the end of writing junk code in order to connect your DB to your UI. Time will say if I'm right.

As I have said in the introduction, I recently left the .Net department in my company. We worked on a very interesting GIS application, using ESRI's ArcGIS line of products.
In the day before our deadline, I've found a really serious but also foolish bug: In many places in our product, the date looked like this: 13/41/07, 20/56/07, and so on.
When I've seen that I immediately knew what happened. Instead of formatting our DateTime data as dd/MM/yy, we used dd/mm/yy. As you probably know, "mm" is the minutes and "MM" is the month, so the dates had the minutes instead of the month.
That was a foolish mistake which caused us to reopen the solution and running many tests all over again. bummer.

What have I learned from this?

1. Always remember the DRY (Don't Repeat Yourself) principle (from the pragmatic programmer). If our DateTime format was stored in one place, the change could be much more simple.
2. Tests have to be more specific: "Check that all fields are OK" is not enought. Many programmers checked these pages/modules and nobody found this bug.
3. I believe that code review should had cover this bug. The code review must be in high quality.