BI review & help

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.