In both SSIS and Informatica, the union transformation doesn't delete duplicate rows. In SSIS it's clear because the transformation is called "Union all", which reminds us the union all statement in SQL which doesn't remove duplicate rows. The confusing part is in Informatica, where the transformation is called Union although it doesn't remove duplicates. One of my teammates got confused this week so I thought it's worth blogging about.

One more tip about ETLs with union transformations: When you'll investigate, maintain or fix ETL it will be much help if you'll know where each row came from. That's why I recommend adding a column named "src" or something like that and store there the source of the row before the union happened.

Lucky for me, I have the opportunity to work with more than one ETL tool in my daily work. In the far past, I worked much with DTS and PL/SQL packages (which is an ETL, but not exactly a tool). Nowadays I work both with SSIS and Informatica so I can compare them in several aspects. I don't think that one is better than the other. It's just that they have different approaches to the ETL mechanism. Anyway, I always expect that the data transformations will behave the same in every tool and I found one transformation that behaves differently between the tools - the Router.

The Informatica's Router, if you're not familiar with, receives a data row and routes it a specific data flow. Its parallel transformation in SSIS is the Conditional Split. As I said, I always thought that they behave the same. In both transformations you provide boolean expressions that determine where the row will be routed. The difference is that in SSIS, "each input row can be sent to only one output, that being the output for the first condition that evaluates to true" (from SSIS's documentation). In programmer's words, it behaves like switch-case statement with break in each condition. In Informatica, "If a row meets more than one group filter condition, the Integration Service passes this row multiple times" (from Informatica's documentation). Back to the programmer's language, it's like switch-case statement with no break inside the conditions.

If you know other transformation that behaves different between ETL tools, I'll be happy to know.

We're now starting to work with OBIEE (aka Siebel Analytics) as our main reporting tool. Our vision is to completely replace it with the old Oracle Discoverer, who will be soon out of Oracle's support. As I'll go on and on with OBIEE, I'll post here things you might want to know. Even if you don't work with this tool you can open your mind and get familiar with new stuff.

My first experience with OBIEE - the installation, was not a great fun. The main problem was the error: Oracle Business Intelligence is not supported on this Windows version. Oracle Business Intelligence is only supported on Windows XP x86, Windows 2003 x86, Windows 2003 AMD64, Windows 2003 EM64T, Windows Vista x86, Windows Vista AMD64, Windows Vista EM64T, and Windows 2000 x86.

The problem is that the installation can't find the windows version because systeminfo is not working. Try to run c:\windows\system32\systeminfo.exe. If you get an error about framedyn.dll, this is exactly the problem. Copy this file from the wbem sub-directory to the system32 directory. Try to run again systeminfo and you'll see it's running. Make sure the PATH environment variable contains the system32 directory and that's it.

Last August, I've seen an Informatica webinar featuring Dr. Ralph Kimball (you can see it here). The topic of the webinar was "Reverse the flow of Data Warehouse Design to Ensure Success". In order to make long things short, what he says is that in the DWH's first steps you don't and can't know what are the full requirements. This is quite problematic, because the first steps of the DWH - its design - are the most crucial steps, where you take the most important decisions which will be hard to change later. His solution is revolutionary and sounds a little bit hallucinatory - Start with the website. After you have the front-end finished, whether it's a dashboard website or just a simple tabular report, and you have the customer's approval, you can start the DWH design and you know exactly what you should do and what you should not.

Thinking of that, we decided that he's right but it's too much. The cost of building a website with a data source behind it which will be changed 100 times in a short period is very high. Instead, we're working in a defferent method - We are working with Mockups.

Our mockups are PowerPoint presentations which mock the dashboard website that will be the project's front-end. We're using a lot of buttons and hyperlinks between the different slides in order to create a feeling of a real website with navigation and interactivity. When we show it to the customer and tell him that this is what his dashboard will look like, at first he's very happy and then the new requirement's phase begin: Can I have this button here? This one more slicer there? What about replacing this chart with this one? etc. In PowerPoint presentation, making these changes is very easy and after a couple of days we have a good understanding of how our front-end will look like. Threfore, we fully understand how our DWH will look like. This is a great method, I recommend you'll start using it in your next project. Try and see for yourself!

• You're not have to use Power Point for this. Visio and even Paint Brush can assist you. Nevertheless, I recommend you to use a tool for this (remember that the mockup will be changing a lot). There are many free & open-source tools to do it.
• You may consider to have the mockup to be your contract with the customer. On the other hand, the final result may look a little bit different. You can draw everything in PowerPoint but in the reality things may be hard to implement.
• After finishing the design phase, don't go over and over to the mockup to keep it updated. Again - The reality is stronger than the first design. When the project will be finished, no-one will care about the mockup.

Panorama views with parameters are very common wherever mass Panorama-based development is made. Some use it within websites and others let the users change the parameters using Panorama WebAccess. When using such views within websites, a common thing that we do is to change those parameters ourselves using the Panorama SDK, where the values are based on the current status of the user, the current time or other parameters which come from outer source such as XML files, Databases, etc.

The main challenge when using parameters within web sites is that you must think of end cases for the parameters' values. When you send a bad parameter to a view using the SDK, the view will sometimes go off and it will stop responding to other things you may do with it in the website. I'll describe it with a life-experience example: We've built a website with panorama paramertized views. Some of the views had FromDate and ToDate parameters, which define the time period for the view to be sliced on. By the way, this is in fact an error by itself - we can use other dedicated functions to do that as described here. The source of these parameters came from a database. The values inside the DB were given from a power user which is the only one who can change them. In some cases, the power user changed these values and the views weren't sliced at all. Debugging it, we've seen that the period between FromDate and ToDate has no data at all so the view wasn't changed. The problem was that once the view was trying to slice on empty period and failed, it stopped responding to other actions (it can be also changed from other controls on the page). So again - think of the end cases when you create parameters-based views.

What I written so far is easy to say, but sometimes you have to do it. The views have to have parameters and sometimes they will get bad data from the users. We have no control over it and we can't deny it. What can we do? A simple approach I use from time to time is to create shadow views. A shadow view is a hidden view (or 1x1 pixel sized) that cannot be seen by the user. Everything that the user tries to do on the real view will be tested before that on runtime on the shadow view. Only if the action succeeded on the shadow view it will be made on the real view. Otherwise, no action will be made on the real view and instead the user will get a message (using Javascript's alert() function, for example) describing why his request cannot be made. The way to check if the user's action succeeded on the shadow view is using output from Panorama SDK functions, but this subject is a little bit wider for this post and it's very case-specific. There are no generic solutions for this problem that I know of, but if I'll find something I'll write another post about it.

To conclude: Think twice about end cases when using parameters inside your views. If you have no choice use the shadow view approach. Write and run tests to check the different values that you'll send to your views.

Make sure that all the Panorama developers has the same version of Panorama NovaView Desktop and that it's the same version of the Panorama server. In addition, check that all the Panorama servers (meaning development server, production server, etc.) also have the same Panorama version. It's very unpleasant when you copy the views from the development server to the production server while installing your new or updated BI project just to find out that some of the views don't show because of that. That's right - it won't cause all of the views to malfunction, it will only affect some. That's the dangerous thing and this is why you need to double check it immiedetly.

in my last post I recommended you to learn about the batch mode feature of Panorama. There's one more thing you've got to know: Check you're code flow very well because if you'll use batch mode within batch mode, meaning that you'll call the EnterBatchCommandMode function after you did it previously, the view will get stuck. Be sure that you don't have such a case.

The solution is to make the parameters update in one action. There are two ways of doing this:
The first one is to use the CallUpdateParametersEx with number of parameters. You can update many parameters in one function call (look in the SDK documentation). The problem with this solution is that the code is not readable.
The second and better solution is to use the EnterBatchCommandMode and LeaveBatchCommandMode functions to make the parameters update in one transaction. This way, the code is much more readable. You can update every parameter in its own CallUpdateParametersEx call. The usage of this function is very simple: Call the EnterBatchCommandMode function before the parameters update and call the LeaveBatchCommandMode after that.

I'm sure that there are more scenarios where this concept can be helpful, so it's important to get familiar with.

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

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.

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.