Difference Between Two Dates Excluding Saturday and Sunday At Report Level In OBIEE 11g

Hey Guys,

Recently we got a requirement to find out the difference between two dates excluding the weekends i.e. Saturday and Sunday. I'm giving the report level formula that can be used to achieve this. Here the Presentation table I'm using is "Time" and the respective date columns are "Start Date" and "End Date". You need to replace these column names according to your Presentation Layer.

-TIMESTAMPDIFF(SQL_TSI_DAY,TIMESTAMPADD(SQL_TSI_DAY,-(DAYOFWEEK("Time"."End Date")-1),"Time"."End Date"),
TIMESTAMPADD(SQL_TSI_DAY,-(DAYOFWEEK(TIMESTAMPADD(SQL_TSI_DAY,6,"Time"."Start Date"))-1),TIMESTAMPADD(SQL_TSI_DAY,6,"Time"."Start Date")))/7*5+
MOD(7-DAYOFWEEK("Time"."Start Date"),6)+CASE WHEN DAYOFWEEK("Time"."End Date")-2>5 THEN 5 ELSE DAYOFWEEK("Time"."End Date")-2 END

Hope this helps!!!

Cheers,
Siva

Filling in Sparsely Populated pivot tables in OBIEE

In this post, i will be explaining how to satisfy some complex requirements like calculating average with respect to all the records in dimension even though there are no relevant fact records.

Most of the reports like simple Order Quantity Report can be done easily with the functionality provided by the OBIEE Answers. Some complex requirements will require additional setup. For example, if instead of subtotaling Order Quantity by summing, we wish to subtotal by averaging Quantities by the number of Customers, whether or not they sold any products.

Here is the initial report that has subtotaling with a sum aggregation rule, [the default].

Here is the same report subtotaling with an average aggregation rule. 

Note that subtotals will be as desired when all cells in the column are populated. Now the FMRADIO against BR Country_Code, for example, does not have the desired results.  The subtotal displayed is 1100.   The desired value is 220 [1100/5] since there are 5 Customers. Similarly for CHARGER against BR, the desired result is 380 [1900/5].

The problem is caused by the absence of rows returned for the respective product lines. See screen shot.

No rows are returned as there are no rows in the fact table for that intersection of dimensions, (i.e. Customer [DARREN] [RAMESH] [STEVE] [DOUG], Country [BR] and products with subtype=FMRADIO).  To solve this problem, we need to join the dimensions in such a way that all values are returned, even if there is no Quantity.
To accomplish this, we will create a physical table alias based on the problematic dimension that is referenced in our report which is DIM_CUSTOMER and we will call this as DIM_CUSTOMER_ALLVALUES

NOTE: The next step is very critical.  The new alias we have created needs to be joined to the other dimension tables.  As there is usually no physical join that can be used, we will just use the clause 1=1, which results in a Cartesian join when executed by an RDBMS.  See screenhots.  Note that this join must be defined as a complex join.

Our physical diagram will look as in below screen shot once we are done creating joins.

Next step is to add this new alias to the logical fact table as one of the sources as shown in the screenshot below.

We will also create a new logical column called “All Values” in the Logical Fact Table and the formula will be just '1' for this new column as shown below. The aggregation rule will be SUM for this column in our case.

Add the All Values column to your presentation catalog.  We are ready to complete our report.

To the original request, add a filter based upon the All Values column. 

Modify the formula of Original Order Quantity column to IFNULL("Fact - Orders"."Original Order Quantity",0) in Edit Column Formula and change the aggregation rule on the column to average.

Now run the report:

We can see that even though there are no values in the fact table for some of the dimensional combinations, OBIEE is substituting the Original Order Quantity as "0" for those and then calculating the Sub Totals properly.

This approach can be used in all the scenarios where we face issues with the fact tables being sparsely populated (Normal in Data Warehouse environments).

Hope this helps.
Siva

OBIEE Services Problem in Windows

Hello All,

Have you ever faced problems with OBIEE services in windows like BI Server not starting up or Presentation Server not starting up?? If yes, this post is for you....!!

Basically, we all will use services.msc to go to windows services and try to start the OBIEE services from there. If any service fails to start, then there would just be a dialogue box simply saying "Service failed to start"!!!???. We really don't know the reason since there is no proper description for the cause of failure. So, now is there a better way of starting the OBIEE services on windows which will give you the exact error causing the start up failure ?? Continue reading to know..!!

When you install OBIEE on your windows machine, all the services that needs to start in order to work in OBIEE will get installed in the installation directory as given below.

BI Server: C:\OracleBI\server\Bin\NQSServer.exe
BI Presentation Server: C:\OracleBI\web\bin\sawserver.exe
BI Java Host: C:\OracleBI\web\bin\sawjavahostsvc.exe
BI Scheduler: C:\OracleBI\server\Bin\NQScheduler.exe
BI Cluster Controller: C:\OracleBI\server\Bin\NQSClusterController.exe

When you have problem in starting any of these services, just go to command prompt and switch to the directory of the service for which you are facing the problem. For example if the BI Presentation Server is not starting up, then go to command prompt and follow the steps below.

cd c:\OracleBI\web\bin

then type sawserver.exe

This will give you the exact log of what OBIEE is trying to do in the command prompt. And you should be able to troubleshoot the problem. Follow the same process for any of the services in which you are facing an issue.

Hope this helps...!!

Siva

Avoid Fact Join in OBIEE Dashboard Prompts

Hello All,

I wanted to share one of the solution i came across to avoid Fact Join in the OBIEE dashboard prompts.

Problem Statement:

When created prompt with constraint enabled feature which has multiple columns from multiple dimension tables, OBIEE tries to restrict the prompt values through fact join at run time, i.e. because the only path two dimension tables could be joined is via fact. So, some of the rows get skipped and will not be shown in the prompt column as all of the dimension records might not be present in the fact table.

Solution Thoughts:

1. We can use the implicit fact column feature available in the Presentation layer. That way we can restrict the join between two dimension tables to single fact table rather than allowing OBIEE to have multiple fact table choices. But the problem with this is, still the fact table that we choose should have all the dimensional combination in it otherwise rows will be skipped.

2. We could built multiple prompts one for each dimension, but this introduces a usability issue of having multiple Go buttons and performance issues too as each Go button click will refresh the whole page.

3. Create one table with all the dimensional combination in it which can be used as a single hierarchy table. This might need an ETL to be developed. Create a new BMM with this table along with any dummy table just to make sure the RPD is consistent. Then create a subject area with this new BMM catalog as base and drag the columns required into the new subject area.

4. Add the surrogate key of the second dimension table into the first dimension table. Make sure that you don’t have duplicates before doing this. Establish a join between these two tables in the physical layer using the column we added. Create a new BMM and bring these two tables into that then establish a complex join. Create a new subject area with this new BMM as base and move the columns required into the new subject area.

After this has been done, the prompts can be created from the new subject area and the join will not go through fact table now and the values will be properly restricted.

Please make sure that

The names of the presentation tables and columns in the new prompts subject area (Which will be used to create prompts) should be same as that of the names of the tables and columns in the original subject area from which the reports are created.

If there are more than two tables on which the prompt has to be created, the approach should be changed accordingly, i.e. we may need to have all the dimensional combinations from these three tables in a single table.

Siva Pratap

OBIEE 10.1.3.4 Grouping the Multiple Dashboards into Dashboard Menu

Hello All,

We might be searching a lot on how & where to club the multiple Dashboards in OBIEE into a Single Pull down Dashboard Menu.
The Solution is as simple as adding the Parameter below.
Before Adding the Parameter

After Adding the Parameter

Solution:
Need to add below Parameter in the Instanceconfig.xml under OracleBIData/web/config

<DashboardMaxBeforeMenu>XX</DashboardMaxBeforeMenu>

XX is the number of dashboards after which we want to group them.

Restart the Presentation Services.


Impact:

Since the Parameter is common for the Instance, it will group any Dashboards under common folder as a pull down menu

Siva

Increase Maximum Drop Down Values in OBIEE

All,

We will be wondering how to increase the number of values in the drop down type prompt. With the Default Installation of OBIEE - Drop down type prompt will only show first 250 values and shows the message (Too Many Values)

Solution:
We need to add a Parameter in the Instanceconfig.xml

1. Add the <Prompts><MaxDropDownValues>XXXXX</MaxDropDownValues></Prompts> where XXXXX is the number of values.
2. Restart the Presentation Services.


Impact:

Please note that providing huge values like 1000, 10000 will have performance impact to the Dashboard.

Best Regards,
Siva Pratap