BI review & help

This is a basic post which I'll assume you know in the posts to come.
Basically, There are two ways of adding JS into the OBIEE dashboard:

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?).

While developing reports in OBIEE, I found myself making the same action over and over again: Styling a column, clicking on "Copy Cell Format", going through all other columns and pasting the format. A better way to do this kind of actions in one shot is by changing the Request XML. Let's say you applied style to the first column and you want to copy it to the other columns. This is how you'll do this:

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.

Learning the schema of the Request XML, you can do many other customizations to your report in one click. You'll see another example in my future post about searching all columns in one click. Thinking about this approach, you may find that it can help you to make your work shorter and cleaner.

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!