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

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.

http://www.miky-schreiber.com/Blog/PermaLink,guid,e52c58d8-4e4f-4077-9656-5242bcf49f7c.aspx http://feedproxy.google.com/~r/MikySchreiberBlog/~3/rd7IKsDxGE8/PermaLink,guid,e52c58d8-4e4f-4077-9656-5242bcf49f7c.aspx Mon, 01 Jun 2009 11:32:34 GMT 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 (<span class="doctype">!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"</span>). When I removed this line, it all seems to work fine. Digging into this <a href="http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">DTD specification</a>, I can't see anything that will prevent from applet tag to appear inside td tag. Strange.<br> <p> </p> <img width="0" height="0" src="http://www.miky-schreiber.com/Blog/aggbug.ashx?id=e52c58d8-4e4f-4077-9656-5242bcf49f7c" /> http://www.miky-schreiber.com/Blog/CommentView,guid,e52c58d8-4e4f-4077-9656-5242bcf49f7c.aspx BI;BI/Panorama;Programming;Programming/Web http://www.miky-schreiber.com/Blog/PermaLink,guid,e52c58d8-4e4f-4077-9656-5242bcf49f7c.aspx http://www.miky-schreiber.com/Blog/Trackback.aspx?guid=db6b1c12-2e99-44de-be5f-627166d2dd65 http://www.miky-schreiber.com/Blog/pingback.aspx http://www.miky-schreiber.com/Blog/PermaLink,guid,db6b1c12-2e99-44de-be5f-627166d2dd65.aspx mikypuff http://www.miky-schreiber.com/Blog/CommentView,guid,db6b1c12-2e99-44de-be5f-627166d2dd65.aspx http://www.miky-schreiber.com/Blog/SyndicationService.asmx/GetEntryCommentsRss?guid=db6b1c12-2e99-44de-be5f-627166d2dd65

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 + '|~~|")');
}

• 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:

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.

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.