Note: In order to understand and implement this you need to know how to add JavaScript to OBIEE. I strongly suggest you to read my older posts about OBIEE manipulation. this feature has been tested on version 10.1.3.4

I've been asked how to create radio-buttons prompt in OBIEE dashboards. The purpose of this is to make the dashboard more beautiful, becasue radio buttons looks better than drop down list (aka ddl). Another nice thing you can do with this is to add description to every option (as I hope to implement in the near future). So, this is how you'll do it:

• In the OBIEE Administration application, I have a session variable called MyProjectWorkingMode (replace MyProject with your project name). This variable controls the mode (or whatever) of the user (let's say it can be red, green or blue). The default value for this variable is red. This variable is important for us because we'll use it to "remember" what the user selected.
• In OBIEE Answers, I created a dashboard prompt which is a drop-down-list. This prompt has the same default value as the variable (red, in our example) and it sets our variable. important: the caption of this prompt must be MyProjectWorkingMode.
• In order for us (or the JS code) to know in which mode we are we'll create a report called MyProjectCurrentWorkingMode. Put there one column which will show our variable's value and one column from the presentation layer (all reports must go to the underlying DB). The expression in the first column will be ValueOf(NQ_SESSION.MyProjectWorkingMode). In the results tab, create a narrative view which will have a div with the id="MyProjectWorkingModeDiv" and inside it write @1. This will give us the current working mode inside the div. In the compound view, make that the report will only contain the narrative view. Save the report.
• Create a new dashboard page. Add Text object and inside it put the javascript code from the file attached to this post and replace anywhere the "MyProjectWorkingMode" with the name you entered. Make sure the name is the same as in the prompt's caption and the div. Don't forget to mark the "contains HTML markup" checkbox. Add the report we created in the previous step in a seperate Section and hide that section (Properties -> Format Section -> Use Custom CSS style = display:none). Add the dashboard prompt.
• That's it. If you did everything right, the JS code will make the ddl as radio buttons.

I suggest that you'll get over the JS code in the file attached in order to understand what I did. That will let you change the implementation if something is not as you wanted.

Last thing: Before you ask me why it doesn't work in your application, make sure you did everything right and that you have the same version as mine. If it still refuse to work, ask for help from a web/JS developer who can debug the code and see what failed. In my previous posts my features didn't work for some and they thought that this ain't working, where it worked for a lot of people who thanked me.

Enjoy.

No-one in my organization knew what is the difference between stop and abort in the Informatica Workflow Monitor. I googled it, and here is the answer in short term: Abort is killing the process in the underlying operating system, meaning that the memory that the session took for itself won't be released. Most of the OS don't have good-enough garbage collectors, meaning that this memory allocation won't be free until you'll restart the whole server(!). The stop command will ask the session to quit whenever it can, meaning that this will take more time.

Use the abort command only if you must stop the session right now. If you see that over time your informatica server goes slower and slower, restart the server in the weekend.

You can see the full answer here.

This is the fifth and last post regarding Itay Braun's  seminar in the SQL & BI conference. In this post, I'll write some notes I collected from the last part of the seminar which discussed SSRS. As you can see, most of the tips are relevent for every reporting tool.

• Scale-Out instead of Scale-In. Meaning that it's better to have multiple SSRS servers on one DB then bigger and better single SSRS server.
• One report should not return all the data. Define and build your report to answer specific answer.
• Use subscriptions. Minimile impact on performance.
• Use cache executions or at least snapshots.
• Visualization (see also my post about dashboard design)
  • Use same colors for for the same business units
  • Use images for better data recognition (e.g countries' flags)
• Monitor the SSRS server. Look at the trace logs, the execution log and the performance counters. You can use Scrubs(free SSRS performance reports).

This is the fourth post about Itay Braun 's seminar in the SQL & BI conference. In this post, I'll write some notes I collected from the third part of the seminar which discussed SSIS.

• Log everything that happens inside the package. Use the SSIS log providers and the event handlers. The effect on performance is not significant.
• Analyize the log data. You can analize total execution time, SSAS partition processing time and much more.
• Log detailed error information about rejected rows. You can even log the actual error row.
• Monitor also the execution of the ETL jobs.
• Data Profiling is a new feature in SSIS 2008. It's used for analysis of the data's quality in the DWH. Go and learn.
• Configure everything you can in the package configuration, such as: tasks, containers, variables, connection managers and data flow components. You can use XML config file, config from parent package, registry configuration or even configuration from a table.
• The Cache Transformation saves data in the cache. It's very useful in lookups.
• Data types: Make them as "small" as possible (better performance). Be aware of precision issues when using money, float and decimal types.
• Don't sort in SSIS unless it's absolutely necessary.
• Sometimes using T-SQL will be faster then running it in SSIS.

Some of the tips are also relevant in Informatica. For example, I always let the DB make the sort whenever possible. Sometimes it's also works with joins. Running packages or procedures can be much more faster than running it in the ETL, but if you look at it from maintenance point of view you'll do it in the ETL. It depends on much factors, such as the time you can have to run the ETL, the knowledge in your team and more.

This is the third post about Itay Braun 's seminar in the SQL & BI conference. In this post, I'll write some notes I collected from the second part of the seminar which discussed SSAS design, but here I'll focus on near real-time cubes. I decided to write an independent post about near real-time cubes because this subject deserves its own place. By the way, I personally think that this subject is very interesting.

There are several solutions to implement near real-time cubes:

1. Processing only the last partition. Pros: Trivial and simple. Cons: Might impact users in terms of resources and cache.
2. Small real-time cube. Pros: Simple and elegant solution. Cons: It's small, so it can't contain much data even if you need to. This is the main reason why people usually don't do it.
3. Backend processing server. Now it's getting interesting. The backend server is only used for processing. The front-end server(s) is used for querying. Pros: Good performance on query time. Cons: You need to take care of syncing the cubes, using XMLA sync, physical file sync (SAN snapshots) or any other technology. The cache is deleted with every sync.
4. Flip-Flop. It's like the previous solution, only here we switch between the servers. Pros: It enables you to check your data/cube before making the switch. Good performance on query time. Cons: You need to take care of changing the connection string in the client side or implement NLB solution. It might disconnect users or you need to take care of waiting for the end of the session and only then change the connection string.
5. ROLAP partition. Pros: No processing at all! Cons: Bad, bad performance.

If anyone have implemented one of these I'll be happy to hear.

This is the second post about Itay Braun's seminar in the SQL & BI conference. In this post, I'll write some notes I collected from the second part of the seminar which discussed SSAS design.

• There's a very common misunderstanding about what is an entity. You can see it when there are a lot of dimensions. If there are "car color", "car manufacturer" and "car creation year" dimensions it seems you've got a problem. If there are dimensions with only key and description attributes (or worse - only one description attribute) then you've got a serious problem.
• Design to Scale: Often the performance problem are caused by the dimensions and not by the fact/cubes.
• Don't develop SSAS with hebrew (or any other right-to-left language). Use hebrew only in translations .I've personally got hurt with this. Maintaining MDX with hebrew is hell.
• Avoid using linked measures. If you have some, consider joining the cubes to one cube.
• There need to be a balance between the amount of the dimensions and the amount of the cubes. If they are equal, you've got a problem. BIDS will warn you (in the 2008 version) if you have two measure groups with the same dimensionality.
• Keep the dimension keys as narrow as possible. Dimension keys based on more than one column is not optimal. Use member properties, such as AttributeHierarchyEnabled, AttributeHierarchyOptimized and GroupingBehaviour.
• Data types is an issue in SSAS. Make sure the data types in the DSV are the same as in the dimension attributes.
• The SSAS engine works best when the foreign key between the fact and the dimension is based on integer columns. Read more about dimension design here.
• Attribute relationships and flexible vs. rigid are critical to performance. As I read more and more about SSAS I understand how much it's crucial to performance.
• Automatic creation of partitions can be a good solution for a wide variety of problems. Note that when deploying your SSAS solution to the server you might override the partitions you created with your code. To solve this, you can use the deployment wizard or to import your SSAS database into solution.
• Each partition should have up to 50M records or up to 250MB. Optimize the partition query. It's only plain SQL query. Get help from your DBA and get a better performance.
• 99% Aggregations will not cover 99% of the aggregation possibilities/combinations. The engine chooses statistically-randomly which aggregation to save. Use the usage based wizard.
• Most of the processing time is the time required for the relational DB to return the results of the SQL query. As before, get help from the DBA, improve this query (note that you can't change it) and get a shorter processing time.
• To solve MDX performance issues, you need to learn what is the difference between the formula engine and the storage engine. I was suprised to see that many SSAS developers never heard of it.
• In MDX Scripts, use scoping instead of conditional calculations when possible.
• Avoid StrToMember or StrToSet functions.

These are some important notes I written in the great seminar by Itay Braun. The seminar was held in the SQL & BI conference in Tel-Aviv two weeks ago. Sorry for the delay, believe me I was busy. The title of the seminar was "Solution - Oriented BI for developers". Actually, it was a collection of lectures and tips on general BI and on SSAS, SSIS and SSRS, mainly on the 2008 R2 version. In this post, I'll bring some notes I collected from the first part of the seminar, which was on general BI development and desing. I'll also bring my personal point of view.

• The solution objective: Taking some reports and showing them is not BI. BI is on combining several sources and bring knowledge based on this integration. Only then it's getting interesting.
• We need to aspire to answer 5 question through our BI solution: What have happened? Why did it happen? What is happening now? What will happen? What do I want to happen? Just for thinking, try to tell yourself which answers does your BI solution give. I think you'll find out (as I did) that you're far away from these five answers. We all are.
• BI is a process, not a one-off project. I always say that BI design & development requires maturity. You need to know that you can't do everything in one shot. There are even things that you can do but it won't be right at the moment. The organization also needs to grow up with the solution.
• You need to decide on an early stage of the project which client tools you going to have. You also need, of course, to decide if you'll have reporting, OLAP or both.
• The ideal BI solution gives an infrastructure and let the customers do their own reporting. The big disadvantage here is performance. You can't know what kind of a killer-queries the user will run.
• A good dashboard gives the manager all the knowledge he needs to have in front of him. It tells him what is the current status of his organization without one click. Do you have this kind of dashboard? I don't...
• Microsoft has a lack of good visual client tools in the BI field. This is where third-side companies, such as Panorama comes.

Enjoy.

SQL Server 2008 R2 has been released not-so-long-ago. You can download the trial and check it for yourselves. Alternatively, you can enter the digital tour or read the free e-book. I started reading the e-book and I promise to update here about the new features of R2.

"Tony Stark once saved his heart - and his life - with the Iron Man suit. Now he's found his heart and forged new life into his company. Stark Industries, by abandoning munitions manufacture for cutting-edge communications. Learn how Oracle helped with the transformation"

Have they losttheirminds? Don't they have better ways to increase their revenues?

I just opened a new blog in the Israeli Microsoft blogs site. The blog will be written in Hebrew and it will be focused on the israeli BI scene. If you're from Israel or know reading Hebrew, you're welcome to visit or the subscribe to the RSS feed.

Do you know how to add JS code to your OBIEE site?

It all started with a bug in OBIEE (version 10.1.3.4) and ended with a wonderful solution that can help us in future problems. Look at the following report table:

Building the Business Model layer and the report correctly will take care that clicking on the EBay link will take us to the Ebay shop page (in OBIEE, not ebay.com) which is prompted on 1/1/2000 and customer A. This happens because the Shop report is also prompted on Customer and Day. The problem starts when we sort the table by the shop name:

Note: all my OBIEE posts are based one upon the other. If you want to implement this, please read and do everything I mentioned in my previous posts.

I think that this is the most impressive development that we did over OBIEE. OBIEE is very lack in paging abilities. You can navigate only forward and backwards in the report. Here is how our google-like paging looks like:

addPageNumbers.js (6.44 KB)

Our users had a weird requirement: They wanted to search all the columns in one action. We thought how to do it. In this post I'll describe the solution. Note that this solution is not good in terms of performance, but this is the best we've got.

1. In every report (/answer/request) you develop, add a column and call it SearchColumn. Define its formula as the concatenation of all the columns in the report. You also need to cast the numeric columns to char in order to concatenate them. For example: FirstColumn || cast(NumericColumn as char) || LastColumn. To make things easier, I written a little C# program that takes the request XML and outputs this column's formula. It's a very simple program so I won't put it here (unless someone will ask for it). You can write this simple program in every language you know.
2. Hide the column (Column Properties -> Column Format -> Hide).
3. Create a filter based on this column. In the operator drop-down-list, choose "contains any". Then, click on Add -> Variable -> Presentation. Enter Term in the "Variable Expr" box (Term will be our variable name) and enter space in the (default) box.
4. Now, we'll let the user to enter the search term. Create a new Dashboard Prompt based on any column you'll like. In the operator, choose "is equal to / is in". Control - "Edit Box". Show Default To - "Variable Expression" and enter Term. Set Variable - "Presentation Variable" and enter Term here too. Label - "Search", for example.
5. All you have to do is to add the prompt in every page you want to let the user search.

Note that the search term will stay until the user will clear the prompt field. As I said, this solution is problematic for number of reason (performance, "why the other page filters too?" and maintenance).

This is quite simple, so I'll let the JavaScript talk by itself (attached). I assume that you know how to add your JS to OBIEE. You need to call the function hideTabs from every dashboard page and edit the hiddenTabs array.

This was written on version 10.1.3.4 of OBIEE. Note that if the DOM will be changed in a future version it won't work.

Non Empty Behavior is a very good optimization method you can use with your calculated measures. It allows you to define a list of measures that their emptiness defines the emptiness of your calculated measure. To understand this, look at the picture (taken from the MSDN):

This is the calculation expressions pane from the Calculations tab in the cube designer. From MSDN:

"The measures you specify in the Non-empty behavior list are used to resolve NON EMPTY queries in MDX. When you specify one or more measures in the Non-empty behavior list, Analysis Services treats the calculated member as empty if all the specified measures are empty. If the Non-empty behavior property is blank, Analysis Services must evaluate the calculated member itself to determine whether the member is empty."

This is not new. NEB is quite old feature in SSAS. The new thing (at least for me) is that NEB can also be used in Panorama formulas! For example, M1+M2 /*NONEMPTY_BEHAVIOR={M1,M2}*/ will apply M1 & M2 as NEB. You can see more Panorama optimization methods here.

If the script is for one page only - Enter the dashboard editor and edit your page. Add a new Text object from the bar on the left. It doesn't matter where you add it in the page. Click on its Properties button and write your script in the text box. Don't forget to start it with <script> and end it with </script>. Check the "Contains HTML Markup" box. There is no need to hide this object because it doesn't contain anything visual. Rename the text object and give it a descriptive name.

If the script is common to more than one page or may be in the future - Create a new request which contains only one column. It can be any column from any table, but I recommend you to use a column which doesn't have many rows (you can use a small dimension if you have one). Click on the Results tab and enter the Narrative view. Write your script in the Narrative text box. Like before, don't forget to start it with <script> and end it with </script>. Check the "Contains HTML Markup" box and enter zero in the "Rows to display" text box. You can save your request now. Every dashboard page that contains this request will run the JS you just written.

How to develop correctly with JS in OBIEE
Don't enter bunch of code in the request. It'll only get you messy once the length of the code will grow. Your code inside the dashboard should only call functions. For example, it can look like this:
<script>
if (myFunction)
{
  myFunction();
}
if (mySecondFunction)
{
  mySecondFunction();
}
</script>

You will write and maintain these functions in external JS file. Take the common.js file (search it under the OBIEE dir in the server) which is called in every page in OBIEE dashboards (don't forget to backup first!!) and add your code in the bottom of the file. Now you have a convenient place where you can edit your JS code. There is one last thing you need to know before proceeding - the browser caches the JS files so you'll need to clear the browser's cache every time you change your code. In Internet Explorer, you can do it in Tools -> Internet Options -> Delete Files -> Delete all offline content. You'll do it a lot while developing, so having a button that does that will be great. You can use Microsoft's IE developer toolbar or another developer toolbar.

Now we can apply new features to OBIEE.

This tip can save you a lot of time because when you don't follow it or you're not aware of it, finding the bug will take you a lot of time.

When you build your workflow or worklet in Informatica, you usually define dependencies between the tasks, meaning that task A will start only after task B finishes. You do it using the "Link Tasks" button in the toolbar. The issue is when you define for example that task C will run only after both task A and task B will finish. In that case, linking the tasks is not enough. You need to edit task C and choose "AND" in the "Treat the input links as" box. The default is "OR", meaning that the task will run when either task A or task B finishes. This can cause you a lot of trouble if task C is using data from task A and B (otherwise, why there's dependency between them?).

1. In the report editor, click on the Advanced tab.
2. Copy the Request XML.
3. You'll need to edit the xml in a text editor which supports macros. My favorite is Notepad++. Paste the xml in the text editor.
4. Now you'll have to learn by yourself how the XML is built. Don't worry, it's very simple. Note that each column is a xml element called saw:column and the style is stored in its child element called saw:displayFormat.
5. Start recording the macro and copy the desired elements to the next column. After that, stop the recording. Pay attention to where the keyboard cursor is before and after the recording.
6. Place the keyboard cursor in the desired place and run the macro several times as much as the columns you need to update. The shortcut to do it in Notepad++ is Ctrl+Shift+P.
7. Paste the xml back in the report: Go back to the page where you copied the xml from, paste the new xml and click on "Set XML". That's it.

One of the (many) missing features in OBIEE is record/row count. In this post I'll show how to implement record count and show it below the table, or report in the OBIEE dashboard. I know that you can find this in other blogs and forums, but I intend to show more sophisticated methods in my next posts that will be heavily based on this, so I recommend you to read it first.

1. Create the record count column in your report: click on any column in the sidebar to add a new column in your report. Edit its formula using the fx button. In the formula text area, enter Max(RCount(XXX || YYY)), where XXX and YYY are two fields which define the key, or lowest granuality of the report. In other words: The combination of XXX and YYY will not show more than once in the report. You can add more columns if you need (column adding is made using the Column button in the "Edit Column Formula" window). If the type of the column is not char, wrap the column with cast(XXX as char).
2. Check yourself: Preview the report. The last column should contain the record count. The value is the same in every row. If it's not working, go back and fix the formula.
3. Hide the formula: Click the "Column Properties" button in the column (the button with the hand), go to the "Column Format" tab and check the Hide checkbox. Now, check and remember the location of the column in the report. In other words, remember if it's the fifth column or the tenth column, for example.
4. Create the narrative: Click the Results tab and in the drop-down list choose Narrative. In the prefix, enter: "Row Count:" or something like that, and in the narrative itself, enter: <div id="RecordCount">@XX</div>, where XX is the location of the new column we created. In rows to display, enter 1. Return to the compound layout and look how the narrative below the table looks like. You can return to the narrative and change its style if you don't like it. Just leave the RecordCount div as it is. We'll use it a lot in the upcoming posts.

Problems with this implementation:

1. The count is made in the BI Server after all records are coming from the database. If you'll check query time with and without the record count, you'll see a big difference.
2. The record count is getting scrambled if the user sorts the table.

I'll post here any updates, if there will be any, about these problems.

For documentation purposes: When you get "Unexpected error occured: Error in Application" error in SSAS while trying to view the cube's calculations tab, you'll need to close BIDS and do some file copy tasks.

It's been a while since I last posted here. It's because it was the end of the year with annoying deadline and a new reporting tool we needed to learn - OBIEE. We learned a lot about it and even extended it, so the next posts will be about that. I'll start with a collection of tips & tricks we collected over the development process. Some tips will be helpful only to newbies, where the others will be also relevant to experienced developers. I divided it to two sections: The administration program and the webby part. Have fun.

The Administration program

• There is no way to auto arrange to ERDs of the physical and the Business model (aka logical) layers. The best approach to make things more simple is to finish the tables import in the physical layer and arrange it by yourself: choose to show all tables, zoom to 50% and move the tables so it will create a nice and clear star schema. after that, just print it out and sent it to your co-developers.
• Naming standards are, as always, very helpful, especially when working as a team. A foreign-key column (non measure) name should end the same for all columns in the project. Measure field name should end with _sum, _count, etc.
• In order to develop in the administration program, the developer should be defined as administrator...
• The "check out objects" dialog box will get annoying after few hours of development. To get rid of it, go to Tools -> Options -> General, and select "check out objects automatically". Pay attention to what you do from now on, because even viewing a table's properties will check it out.
• You can always convert a table to a view in the physical layer. Right click on it, properties, and choose "Select" in the table type. Then, write the SQL query.
• When working online against the repository and making an action against the DB (such as row count or view data), the connection to the DB is made using the BI server's data source. Only when working in offline mode the DB connection is made using the local ODBC.
• When dragging a physical table into the logical layer it won't save the tables relationships. Only when dragging a number of tables into the logical layer the relationships will also be imported.
• Circular relationships between the tables are not permitted in the logical layer but permitted in the physical layer.
• Always use complex join in the logical layer. The BI Server supposes to understand in which physical relationship to use. Change it only when it doesn't.
• When defining one-to-many join in the ERD you drag the line from the single to the many table.
• In a dimension with a hierarchy (not flat one), the lowest level should contain the table's key column. Every column which won't be associated to a level will be automatically associated to the lowest level.
• In 90% of the cases, all the measures will have sum as aggregation rule. You can define it in a single shot by selecting all the measure columns, right click on them and choose "Set aggregation".
• The best practice is to build a dimension over any dimension-table, even if it's a flat hierarchy. Also, you should connect each fact table to its levels in the dimensions. You enter into the fact's source's properties and in the content tab you select the logical level for every dimension which is connected to the fact table.
• You can quickly build a dimension by right-clicking the dimension-table and choose "Create dimension". It won't always be good enough, but it's not so bad.

The Webby part (Answers & Dashboard)

• The error "Incorrectly defined logical table source (for fact table XXX) does not contain mapping for YYY" means that the BI Server cannot reach from table XXX to table YYY. It can happen because of two reasons: The first one is that there are no relationships defined in the logical or the physical layers (or maybe both). Note that the connections can be indirect. The second one is that the relationships doesn't makes sense. For example, going from a fact table to a dimension table through many-to-many table cannot happen. You'll see this error many times as a beginner and it can be quite frustrating at first. You need to understand that the BI Server can only understand simple and classic star schemas. More complex ERDs need to be split by using alias tables.
• While developing a complex report (answer, request, you name it) with many columns, save it from time to time. Don't forget that you're working with a webby application. Closing the window won't save your work and won't alert you if you didn't.
• After styling a column in the report, you can copy the style to other columns. In the styling window there's a "copy format" button. You can click on paste in the other columns' style windows.
• In order to make a customized report, you can use the Narrative view. To insert a column's value, use @ and the column's location. For example, @2 is the placeholder for the second column. You can also apply javascript in the narrative to make dynamic columns and effects.
• When you add a column in a fact-based report, bring it from the dimension table and not from the fact table. This will help you when you'll make navigation from this report to the dimension-based report.
• The last button () in the navigation toolbar won't go to the last page as expected but will bring all the rows. In a case of a big table, this operation will take a long time and will make the browser freeze.
• After a change in the RPD you need to update the answers site: Click on "Reload Server Metadata" below to side bar. Then, Settings -> Administrator -> Manage Sessions -> Close all cursors.

This is definitely not the last post about OBIEE. The next posts will be much more interesting and helpful. I promise!

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.

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.

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.

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.