After a long break from blogging, it's time to get back on the course. In the last weeks I worked hard on two BI projects so I didn't had any time to write, but the good news is that I learned a lot and now I have more to share. Now, let's talk about the Panorama Dashboards web site.

When you build many pages with the NovaView Dashboards, you often find yourself repeating the same code and actions in different pages, which stands in conflict with the DRY principle. The solution to this problem is to maintain a global javascript file and reference it from your custom code. That way, you'll edit only one js file all the time. Take my advice and put the file in VSS or any other source control. Every page's custom code will be only the code which is unique to the page. The bigger problem is that during development, you still need to create many pages, reference them to the global js file and make very page's own manipulations. I don't have a silver bullet solution, but the next tip can save you much development time.

Warning: Before doing anything with your DB, back it up.

Behind the Dashboards web site, there's a little database called PanoramaDashboard which contains all the web site's data. In this post, I'll discuss the Tabs table. The Tabs table is maybe the most important table in this DB. Every row in that table is a page in the Dashboards web site. When I create a new page that is very similar to another one, I click on "Create a copy" in the web site (on design mode, of course) and then I run the following SQL command in the DB:

use [PanoramaDashboard]
Update Tabs
set
CustomCode = (select CustomCode from Tabs where Id=YourPreviousPage'sId),
UseCustomCode = 1,
Name = TheNewPage'sName
where Id = (the new page Id. You can see in the page's address in the address bar)

After that, edit the custom code from the DB (very similar SQL command). You'll have to enter manually the new Applets names/Ids. Don't try to duplicate the page using the DB because it won't work.

If you did some mess with the order of the pages in the section or you just want to re-organize the pages, you can re-order it using the same Table. Just change the FatherId of the page. Note that a father must be a folder, so you need to set Folder=1 and that a page with no father will have FatherId=-1. Again - don't try to enter manually a whole row because it will break the web site (you'll have to remove the identity from the Id column and that can cause you much trouble).

Enjoy. and be careful.

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.