This post is a collection of some tips we collected in the last week regarding Panorama views development:

• You should always check that the view is loaded with data. Sometimes it will be loaded with no data because of lack of data in the DWH or because the view is sliced on a member with no data. It confuses the applet and from that point it won't always continue to function. From Dashboards developer point of view, if the view is loaded with no data, replace the view with a descriptive message. Dashboard page with "No Data" message will make the users think that something is very wrong with the BI system.
• Panorama has some issues with formats. Define the formats in the OLAP. Panorama will use them as defined in the OLAP.
• Avoid as much as you can from sending parameters to views. When adding parameter to the view, think twice if you have a way of getting this parameter's data from the OLAP. You'll be suprised to find out that in many cases you'll be able to do that.
• If you slice your view as a result of a user event, try not to use parameters. Try to use the callFilterGridMembers function or callSliceByMember function.
• Slicing a view on a member which has no data can cause the view to stop working. What you can do is to create a hidden view (1 pixel x 1 pixel) which is the same as the first view. Whenever you want to slice the view, make the slicing on the hidden view first. Using the Panorama's SDK, find if the view has data and only then slice the original view.

Thanks to Itay Segal for the tips and for the help.

What will happen when new members are added to the dimension which is shown in your Panorama chart?
The answer is one of the following:
A. Scroll bar will be added in the bottom side of the chart.
B. The chart will expand itself. If the chart is in a dashboards page, it may ruin the page because the view will extend beyond its area.

That's why Panorama's default setting for "Auto Select all X-axis Members to Chart" (and Y) is false, meaning that the chart won't expand even if new data arrives.

The other side of this default setting can be bad. Last week, we had a situation where one of our dashboard pages acted in a strange way: The page contained two views, where the first view is a crosstab and the second one is a chart. When the user clicked on a row in the first view, it sliced the second view on the desired member in the Y axis. What happened is that after the chart showed two members in the X axis, for example, It didn't show three or four members in the X axis, no matter what. We didn't underatand what went wrong and went home with sad faces on Friday. This morning, we realized that we forgot to enable the Auto-Select setting.

The BI team and the source system team has a great bond between them. When the source system's team wants to change the system's logic or the physical tables, they must inform the BI team as soon as possible, because the BI team must change their system too. Otherwise, the ETL will fail and the users will see old or no data, or in the worse case the users will see incorrect data.

On the other hand, when the BI system shows data which is not the same as the data in the source system, the responsibility of showing that the BI system is OK is on the BI team's shoulders. It needs to investigate the problem and see if the error is on the BI side or in the source system side. Maybe there is no error at all and the difference is caused by difference in the logic.

Sometimes, we (the BI team, of course) investigates the data problems and find that the error resides in the source system itself. Then, we need to make sure that the error is fixed in the system, whether by the IT staff or by the power users. The important point here, and this is why I decided to write this post, is that the change must be done first in the development environment of the source system. Then, we'll run the ETL process and see that everything is fixed. This is important because sometimes we think that the change will make things right and it's not. That's why everything should be changed in the development environments first in both systems.

Only after we've seen that the data is fine, we can make the change in the production environments. Last tip: Make sure that the change is also made in the source system's production environment. Sometimes the time interval between these events is long and the source system's team just forgets all about it. Write it down as a task before going to production.

Last Week we had a funny case which took us much time to understand.

In our development environment, we like to have the same source data for a long period of time. This way we're familiar with our data and we can expect the numeric results in the higher layers (OLAP, dashboard, etc.). When we have a stable environment and we think that our system gives the correct data, we ask our infrastructure team to refresh our sources, meaning that they bring new production data to the development environment.

The situation that we had last week is that we refreshed our sources and from that point the data didn't make any sense. We asked the infrastructure team if they're sure they refreshed the data and they said they are sure. We checked the system and the data many times and seen that the data is different from what we had but still it didn't make any sense.

After much frustration, one of us made a suggestion to write sql query in this form:
select max(some_date_field) from fact_table

Then we were all amazed. After much time of sisyphean work we realized that our data is not new at all. Moreover - our old data was old too. The maximum date in our main fact table was half a year ago. The reason was that we took our data from a legacy system which runs a job that exports their tables into files, and our infrastructure team imports these files into our source tables. The problem was that no-one ran the job so the files themselves were old, meaning that our refresh activity means nothing.

Conclusion: Never trust your sources. Always run a sql query like mentioned above to check that you have recent data and that the data you think you have is the data you have. You can think of more queries that validates that what you have is what you expect to have. Run them every morning to make sure you're ground is stable. It's only a matter of seconds and it's worth it.

May the source be with you.

The error function in Informatica can be very useful when you want to know why you have failed rows without failing the whole process. The use of the function is ERROR(string). Calling this function will make the error appear in the session log and the row will be ignored. For example: IIF(my_date_field > sysdate, ERROR('Futuristic Date'), my_date_field). This will ignore futuristic dates and show them only in the session log. By the way, you can declare default value for the port and the process will replace the ERROR with the default output.

A wonderful idea I heard of is to turn to full screen mode in Internet Explorer when entering the Dashboard site. It can make a better user experience. Try it yourself and you'll see the huge difference.
How will we do that? We will add a JavaScript code to the first page of the dashboards site and after that we'll ask our system administrator to enable this script for us. Let's get to work:

Step 1 - The JavaScript

Create a new HTML component in the dashboard page. Edit it and click on the "View Source" button (the one with the <>). Enter the following code:
<SCRIPT>
var wscript = new ActiveXObject("Wscript.shell");
wscript.SendKeys("{F11}");
</SCRIPT>

It will simulate the user hitting the F11 key which will turn the IE to fullscreen mode. The only problem is that when viewing the page, you'll see this message:

This takes us to step 2.

Step 2 - Enabling ActiveX

First, I'll show you how to do this on your local maching and then you'll ask your system administrator to enable it on all the machines in the organization using distribution. Enter the Tools menu in IE and hit Internet Options. Click on the Security tab and make sure the "Trusted Sites" zone is selected. Note that the Panorama Dashboards site is already defined as trusted site (if the initial installation of Panorama Dashboards made according to the installation manual. If it's not, you have a problem). Click on "Custom Level" and Enable the "Initialize and script ActiveX ..." option:

Now, you'll see that there's no promting for ActiveX controls. Show this to your sys admin and ask him to make this happen on every user's machine (using distribution, of course). As I said, the dashboards site is a trusted site so I can't see any problem to enable this. The result is very beautiful and can make a lot of users happy. Note that you can also add a button in your page that will call the same script in order to return to normal mode.

Enjoy.

When adding parameters to your view, you'll see that they appear in the upper-left corner of the grid/crosstab. In the NovaView Desktop program it can be tolerated, but in the Web Access or in the Dashboards web site it cannot be. It's very annoying and we can't let the users see our inside use of the parameters. What can we do?

The solution is very simple: We need to change the skin of the view/dashboards page/dashboards site (depends on how you work) and make the grid corner font's color identical to the color of the grid's background. That way, the users will not see the text in the grid's corner. The way of doing it is also not hard:

Remember: Always backup your files before modifying them. In the panorama folder, enter E-BI/Config/Skins and enter your skin's folder. In the classic way of work, you're using the default skin which can be changed in the Dashboards settings section. I recommend you to make a new skin out from the default one (see here), update the skin's name in the Dashboards settings section and not touching the default skin itself. In your new skin, change the GridCornerFont setting so that its color will be the color of the grid's corner's background. You can see the color of the grid's corner in the GridTopLeftBackground setting. For example, if GridTopLeftBackground=(194,210,226), then if you set GridCornerFont=((Arial,1,R),(194,210,226)) then no-one will see the text over there.

Enjoy.

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.

Trying to build ASP.NET page with Panorama applets, I could not understand why the applets appeared blank when I put them in tables. After a while, I've found that this got something to do with the DOCTYPE declaration that each aspx has in its head (!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"). When I removed this line, it all seems to work fine. Digging into this DTD specification, I can't see anything that will prevent from applet tag to appear inside td tag. Strange.

A new desire came from one of our customers. The request was to have the ability to search in Panorama Crosstab. The first solution I thought of was searching the grid in iterative way and it worked fine using the Panorama SDK. After that, my friend Boris came with another simple and elegant solution: We can use Parameter in the Panorama view and highlight the number which was defined in the parameter. In this post I'll explain how to implement this.

1. Create a new view using Panorama NovaView Desktop and make sure you see the grid in the view.

2. Define a new parameter: Click on View -> Paramaters, and click on the "Manage Parameters" button. Click on Add. The default type is Number and this is exactly what wee need (for now). In the name, type Highlight and in the Default Value type a number that you see in the grid (this is the number that will be highlighted later). Let's take 0 for example. Click on OK twice and close the little Parameters window.

3. Create a new Exception: Click on Data -> Exceptions -> Exceptions... -> Add. Click on Next and then choose "Custom Exception". Click on "Edit Exception" and there write the following formula: [Measures].CurrentMember = [[Highlight]]
This will simply select all the cells with the number that we defined earlier in the Highlight parameter. Click on OK and click Next. In this step, define the style of the highlighted cells. I picked red color and Bold font style. You can click on Finish now and then click OK. Open the small Parameters window (right click in the crosstab's corner and choose Parameters) and click on "Apply Changes". Now, you will see that all the cells with 0 are highlighted. If you don't see it, check that you did all the steps correctly.

4. When we will show the view to the user, we don't want to show him anything highlighed when the view is loaded. This is where a little trick takes place: open the small Parameters window (right click in the crosstab's corner and choose Parameters), double click on the Highlight parameter. Choose String as the parameter type (on the right part) and in the Default value, enter abc. Click twice on OK and then on the apply button and you'll see that now the highlighed cells are regular ones.

5. In the dashboards page, or in the web page you created using the Panorama SDK, create a button that will call the function searchGrid. Just add the button the property onclick="searchGrid('master')", where master is the applet's name. this is the code of the searchGrid function:

function searchGrid (applet) {
var reply = prompt('Please enter the number to search','');
eval(applet + '.CallUpdateParametersEx("P|~|Highlight|~|' + reply + '|~~|")');
}

Another tweaks I implemented and I didn't write in this post in order to make it simple (for advanced developers only):

• You can search all the views in the current web/dashboard page. Just call the function for every applet, but make it in Batch mode.
• You can search all the grid even if the user doesn't see all the rows. You can tell him if the number he searched for is in there or not.

That's all. Test your new page and enjoy. For every question about this and anything else, you can leave a comment or write my mail.

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.