OBIEE Interviews: Reports

Standard

How do we create level based measures in Answers?

Level based measures can be created in the report by aggregating the fact by the level. eg SUM(Facts.Revenue BY Time."Year")

SELECT Time."Year" saw_0, Time."Month" saw_1, Facts.Revenue saw_2, SUM(Facts.Revenue BY Time."Year") saw_3 FROM "Sales Applications" ORDER BY saw_0, saw_1

How do we configure a report with all its Sub Totals displayed at the bottom?

eg I have a report with 3 columns class, Target/Achieved and Revenue. We need to display for every class its target in 1 row achieved in the next row. Sub Total for Target and achieved at the bottom of the report.

Create the report with all the 3 attributes add one more column sort order with column formula 1. Union this report with other report with columns Total(hard coded), and the other two columns with the sort order column taking value 2 Sort with Sort Order ascending and hide the column. Do a conditional formatting for all columns with if class='Total' eg like blue background etc.

Report Configuration

Thus we can achieve a report with all Sub totals at the bottom of the report

Tough

How do we use COMPLEX FROM CLAUSE in Advanced SQL Clauses in OBIEE?

Reports from multiple subject areas can be combined by joining the common attributes by putting the individual sqls in the from clause and specifying the joining conditions. This query is similar to sql query with inline views.

Logical SQL of individual reports

SELECT Dims.Class saw_0, Facts."# of Customers" saw_1 FROM "All Application" ORDER BY saw_0

SELECT Dim.Class saw_0, Dim."Year" saw_1, Fact.Revenue saw_2 FROM "Sales Application" ORDER BY saw_0, saw_1

Logical sql of the combined report

SELECT A.saw_0 saw_0, b.saw_1 saw_1, A.saw_1 saw_2, B.saw_2 saw_3 FROM (SELECT Dims.Class saw_0, Facts."# of Customers" saw_1 FROM "All Application" ) A Full outer join (SELECT Dim.Class saw_0, Dim."Year" saw_1, Fact.Revenue saw_2 FROM "Sales Application" ) b on a.saw_0=b.saw_0 WHERE A.saw_0 <>'Unspecified' ORDER BY saw_0, saw_1

It should be noted that we want to combine the two report via the common attribute class and full outer join is introduced to fetch all the records. Thus we can combine multiple reports via a Join.

How do we combine two reports from different Subject areas by Combining the common attributes?

eg We have Customer,Employee, Revenue from Sales Analysis Subject Area and Customer,Employee, Activity Cost from Activity Analysis Subject Area, we need to create a single report with Customer,Employee, Revenue and Activity Cost.

Create a union all report from these two Subject areas and build a pivot table with aggregation of max.

Thus we can combine two reports from different Subject areas by Combining the common attributes.

How do we create a report with metrics of Multiple Granularity?

eg we have Account,project and services as dimensions and period and revenue as metrics. One Account can have multiple projects and each project can have multiple services. Period is at a Project level and revenue at each Project service level. Create a report at account level with these two metrics summed up at appropriate granularity.

create the report by adding all attributes.

for the period metric apply the formula as shown in the below figure