BI review & help

When you develope a big Panorama Dashboards site, you'll have a lot of javascript code in the background. We use JS to call the Panorama SDK functions and methods, make the server side and the client side work together and to make the website dynamic and user-friendly. After few days, you'll see that you have a lot of code out there, so you must organize it (if you didn't do it in the first place). My friend Doron wrote a great post about JS development guidelines which can help Panorama Dashboards developers and any big website developers.

Very easy. In Sql Server:
Select top n * from my_table
In Oracle:
Select * from my_table where rownum <= n

It can be very useful in many many cases. For example, you're designing DWH over a system and you're looking at a certain field in one of its tables. You want to know which values this field contains, but fetching "select distinct my_field from my_table" takes too much time. Instead, if you believe know that the data is well distributed, you can use "select distinct my_field from my_table where rownum <= n". Use 1000 for n in the first trial and add one zero in the end of the number n every time until you got a query that takes too much time than you want to wait. after you got the n you can live with, can use the values you have in your query result.

We're now beginning a quick session of Panorama Dashboard development using the 5.5 version. The Dashboard site is written in ASP, so it seems very native to write our custom code also in ASP. On the other hand, writing in C# is much more fun, so we decided to try and see if we can make the server side code be in C#. We found that it is indeed possible. You can place an iframe with ASP.NET page and reference the dashboard page from it using JavaScript (start from page.parent and go on from there).

This way you can enjoy the two worlds. In the next posts I'll show some examples of what you can do using this method.

There are some things you can only learn in the hard way. It didn't happenned to me personally but to my team friends, so I consider it as it is my bad.

We upgraded our ETL tool - Informatica, from version 8.5 to 8.6. We had to run some tests to see that the results are the same. So, what we did is to save the result table from 8.5 in Excel, save the result table from 8.6 in Excel and then compare them using Excel-built-in functions. The only problem is we didn't pay attention to the places where zero and null interchanged. This happened because the two versions act differently where null values take places in aggregation functions. For example, when there is a sum function and it aggregates only on null values. In one version the output is zero and in the other the output is null...

This can also happen in other tools and technology. For example, in OLAP cubes the difference between zero and null is the difference between seeing the member of the dimension on the screen and not knowing of its existence.

For conclusion, always be aware to this point and don't forget to check it.

Here are some tips we collected over the years about dashboard design:

Page Layout

• Less is more - don't put too many views in the page.
• Rule of thumb - no more than five reports in one page.
• Don't use scrolling - the average user won't scroll down the screen.
• Position in screen - some researches made about this subject and here are the recommendations:
• Top-Left - it's the part of the screen that the user looks at first. Put there the most important data.
• Center of the screen - the part the user looks after the top-left. Put there the second-most important data.
• Top-Right, Bottom-Left - Neutral parts.
• Bottom-Right - The user won't pay attention to it, don't put there important data.
• Fixed menus in every page.
• Small amount of navigation targets in every page. Too much navigation paths will cause confusion.
• Concentrate on the main page - in 90% of the cases the user will stay there.
• Add graphic components and highlight them if necessary.
• Blue color only to links (and underline, of course).

Views Layout

• Two digits after the decimal point - in non-integer number, put only two digits after the decimal point. The human mind can't understand more than that.
• Focus on the clarity of the data and not only on its beauty. For example, 3D pie charts are very beautiful, but flatting them will make them more clear to the user.
• Measures have meaning only when compared to other data. Don't put stand-alone measure.
• Pay attention to graphical change between the data and not only colors. Remember that there are color-blind users.
• Text is more clear than icons.
• Use the San Serif and Arial fonts. They are the most readable to the user.
• Align the text only to one side and not to the middle. It seems better to programmers, but users want their text aligned to the left or to the right.
• Colors - don't use too much color. The dashboard page is not a jungle. Use colors of the same family.
• Put dark text on bright background the vise versa.

And to conclude - use CSS whenever you can. It will save you much time and effort.

In many cases, extraction of Panorama view's MDX code is necessary. For example, in order to check whether the performance bottleneck is in Panorama or in the OLAP server, you can take the MDX code, run it in SQL Server Management Studio and compare run times. You can take it further more but I'll leave it for future posts.

In Panorama Desktop program, click on Tools -> Direct MDX... and then CTRL + ALT + V. Then, you can copy it and use it in any way you want.

I'm glad to announce that two new Panorama forums were opened in the last days:
The first one is Panorama's Technical forum (English only). From now on you can get answers using this forum and see another users' problems and answers.
The second one is independent Panorama forum (Hebrew only). This forum was created by Michael Ra'am, ex-Panorama consultor. Its purpose is to be a place for sharing knowledge and ideas.
I believe you'll see me in both forums. See ya!

In some of our projects, we develop the Panorama views in the development environment along with the Data Warehouse, the ETL, the Cubes, etc. That's because the customers want to see how their product will look like before we deploy the views in the production environment. So, how do you deploy Panorama views from one environment to the other?

1. Create the new book - If it's a new briefing book, create it using the Panorama NovaView Administrator program. If it's already exist you can skip this step.
2. Copy the content - The book's content is by default in c:\<Panorama Folder>\E-BI\books\<Book Name>. Copy the content of this directory from the dev machine to the production machine. This is not enought because the views are still looking at the dev environment, so:
3. Change the view's properties - You need to have a very simple program (let's call it PanoramaDeployUtil) that iterates over all the views in the given folder (and its sub folders, recursively) and change its properties. I recommend opening the view's file using xml reader and change the element \pnView\Root\Cube\Properties. You need to set its properties CubeAddress, CubeName & CubeDB according to the new environment's values (CubeAddress is the server address). Just run this program and the views will look at the new environment.
4. Check - check yourself and make sure everything is ok by opening Panorama Web Access or Panorama Desktop and see that the values shown are the production's values.

Know that you can always open the Panorama Desktop and change the views one by one by hand.

Enjoy.

You should know that when you run a program by starting a job with a CmdExec step, the directory in which the program is running in will be c:\<windows dir>\system32. How can this affect you? For example, I created a .Net console application that has a settings file with it. When I ran it using the SQL Server Agent, it couldn't find the settings file (worse - it used the default settings and that caused many trouble finding the problem). After some research, I found that it's looking for it in the directory I mentioned.

I've been thinking lately about the new Microsoft Chart controls which are based on the Dundas acquisition (made in April 2007). What is the meaning of this to us, the BI developers?

Until now we were always counting on the abilities of our BI products. Let's take Hyperion/Oracle Essbase for example. Let's say I want to have a special graph of a new type or a new feature in a graph. I couldn't do it at all, because the product's code is closed (someone has to do money, doesn't it?) and I can't add any more graphs or features. There are some products where I can do things like this. For example, in Panorama NovaView I can build a new KPI type or doing a sophisticated visualization using JavaScript and Panorama SDK, but that's a lot of coding.

Now, we have the ability to write graphs by coding them without large amount of code. We can customize them as we want and we're not limited by any product. The drawbacks are maintenance and knowledge that we need to have here, but these are things that we need in every product anyway. I didn't learned this framework yet so I can't tell where are the limits, but they seems pretty far. Alex Gorev is writing about it in his blog (web, rss) so you can learn more about it there. It will take time to see if it affects the BI development world, so all what left to do is to sit and wait.

Today I had a very disturbing coincidence.
My friend Ariel worked on a SSAS solution with no version control (we're using VSS). Instead of using that, he developed by opening the database on the server. I told him that he must fix it and we must have a recent version-controlled solution. In the past we asked Microsoft support how to do that (we lost all our vss files and had only the databases). They simply said that it's not possible. Ariel has found today that it can be made very easily using File -> New Project -> Import Analysis Services Database, as you can see in the picture: