BI & Warehousing

Oracle BI EE and Essbase Connectivity – Report Based and Essbase based Grand Totals – Answers Based Aggregation

Venkat Akrishnan - Wed, 2009-05-20 05:13

This post here by Christian prodded me to write about another interesting feature in the BI EE and Essbase Connectivity. As you would probably know BI EE supports report based grand totals/sub-totals in a table view. There are 2 types of totals. One is BI Server based totals wherein BI Server would do the totalling on a result set. The other is data source specific totalling wherein the query is fired back to the underlying data source to obtain the totals. For example, lets just quickly import the Demo->Basic cube in to the repository and build a very simple report as shown below.

As you see, it is a very simple report containing a report based total and a sub-total at the market level. Before going further, lets look at the outline of the Basic cube first. As you see, every dimension top member is set as a stored only member.

If you look at the MDX of the above report, you would notice that 3 MDX queries would be fired. One for the base report , one for the grand total and the other for the market sub-total.

The aggregation for the Measure Sales is Aggr_External in both the physical and logical layer. In Answers, the aggregation is set as default. Now, lets go to the outline and convert the Year Dimension top member to be a label only member as shown below.

Now, try running the same report above. You would notice that the report level totals and sub-totals are totally wrong as shown below.

The main reason for this is the fact that since we have converted the topmost member of the outline to be label-only. So for the Year dimension, it will always pick the Qtr1 value instead of totalling all the quarters. For report developers, this would turn out to be an absolute nightmare considering the fact a report based total is created under the assumption that, the totalling is done on the report and not at the data source level. Now, from Answers lets change the aggregation os the sales measure to SUM.

And look at the report.

Basically, a report level SUM ensures that all the custom aggregations/totalling occuring in a pivot table/table are done at the report level instead of at the Essbase layer. So by default, ensure that you always have SUM at the report level to ensure that you do not get wrong answers especially for totals and sub-totals.

This should have given you an idea of how the aggregations at 3 layers (Physical, BMM and the Answers) can affect a report. I would cover the usage of Report Aggregations across different BMM and Physical Layer aggregations in the future.

Categories: BI & Warehousing

Data Mining Survey - Last Call

Marcos Campos - Tue, 2009-03-24 08:41
Rexer Analytics has just issued a last call for its annual data mining survey. This is a pretty nice survey that provides a great deal of valuable information about how data mining is used and who is doing it. To participate, please click on the link below and enter the access code in the space provided. The survey should take approximately 20 minutes to complete.  At the end of the survey you Marcos
Categories: BI & Warehousing

Job Opportunities

Marcos Campos - Mon, 2008-11-03 09:46
The Oracle Data Mining group has two openings for talented software developers and machine learning experts. Take a look at these links (IRC1133401, IRC1143559) for more details.Marcos
Categories: BI & Warehousing

Oracle BIWA Summit 2008

Marcos Campos - Tue, 2008-10-28 04:10
The Oracle BIWA Summit 2008 is approaching  (December 2-3) . It will be held at Oracle World HQ, Redwood Shores, California. This is the second event of its kind. Last year's event was a great success and lots of fun (see details here ). This year's keynotes include Jeanne Harris (co-author of "Competing on Analytics") and Usama Fayyad (legendary data miner).  Here are some information and linksMarcos
Categories: BI & Warehousing

Collective Intelligence 1: Building a RSS Feed Archive

Marcos Campos - Mon, 2008-09-08 22:10
For a long time I have thought that we needed data mining books written for developers. Most data mining books are written for business or data analysts. Given that, it was a pleasant surprise to read Programming Collective Intelligence: Building Smart Web 2.0 Applications by Toby Segaran. The book provides a good discussion on data mining concepts anchored with interesting examples. It also Marcos
Categories: BI & Warehousing

Data Mining in Action: Oracle Sales Prospector

Marcos Campos - Fri, 2008-08-22 12:10
I firmly believe that a major trend in applications is the incorporation of analytic-enabled functionality. Users want more than just reports or a replay of the past. Users want to have insights and their attention directed to key points. This is where analytics can make a big impact across all types of applications. Notice that I am not proposing exposing analytical capabilities (e.g., data Marcos
Categories: BI & Warehousing

Matrix Inversion Using PL/SQL

Marcos Campos - Mon, 2008-08-18 07:18
Recently someone asked me how to invert a matrix using the UTL_NLA PL/SQL package. This can be done by solving a system of linear equations AX = B like the ones I described in this post but setting B to the identity matrix. I thought that the question was interesting enough to deserve its own post. So I am replicating the answer here for those interested. The UTL_NLA package has many different Marcos
Categories: BI & Warehousing

Oracle at KDD 2008 and KDD 2007 Pictures

Marcos Campos - Fri, 2008-08-15 22:34
It is that time of the year again. In about a week I am going to be attending the KDD (Knowledge Discovery in Databases) 2008 conference (conference website) along with some other Oracle colleagues. KDD is one of the primary conferences on data mining. This year it will take place in Las Vegas, Nevada, from August 24 to 27.Oracle is a Gold sponsor for the event and will have again a large Marcos
Categories: BI & Warehousing

Why do we not report by the Gregorian calendar?

Dylan Wan - Tue, 2008-07-01 02:37

  1. The number of days is different in each calendar month.
  2. The week and month cannot be aligned. The number of weekends is different in each calendar month.
  3. The number of working days is different in each calendar month. It ends up that the number of days in each quarter is also different.
  4. The period closing day will fall into different days in each period. The accounting department prefers always close the period by a given day in a week, such as Wednesday or Friday.

Categories: BI & Warehousing

13 Period Calednar

Dylan Wan - Tue, 2008-07-01 02:14

In this posting, I will discuss the following topics:

* What is the 13 period calendar? * Who uses the 13 period calendar? * How is it different from the 4-4-5 calendar?

Read the rest of this entry »

Categories: BI & Warehousing

Essbase and IBM DB2

Dylan Wan - Mon, 2008-01-28 13:40

I read an interesting article, IBM DB2 Minus OLAP from the SQL Server magazine. Essbase used to be OEM-ed and re-branded by IBM as IBM DB2 OLAP server for ten years. The relationship stopped two yeas ago.

Many DB2 customers actually built their custom analytics applications on the top of Essbase.

Categories: BI & Warehousing

Recap Post

Marcos Campos - Thu, 2007-12-06 12:11
For the past couple of months the blog took a back seat. Basically, since KDD, I have had very little time to write. I have been on the road quite a bit and my trip to KDD unleashed a number of research ideas that I have been following up. I will post on the latter over time as the results mature.I have also dropped the ball answering many of the emails and comments I have received. I have caughtMarcos
Categories: BI & Warehousing

Oracle BI Applications and Embedded BI, Part II

Dylan Wan - Wed, 2007-12-05 13:04

This is a topic I wrote in six month ago. In the Part I of this series ofarticles, I mentioned that a warehouse like architecture is required ina heterogeneous environment. I want to elaborate more about this. Inthe future posts, I will also describe the integration technology Ilearned for supporting the embedded BI.

Read the rest of this entry >>

Categories: BI & Warehousing

Key Roles involved in a BI Data Warehouse Project

Dylan Wan - Fri, 2007-11-30 14:10

To develop ordeploy a BI solution for your organizations, you need to have the rightpeople involved in the time time. Here are typical roles involved in aBI data warehouse project.

  • Project Sponsor
  • Project Manager
  • Functional Analyst
  • SME
  • BI Architect
  • ETL Developers
  • DBA

The job description and responsibilities are listed in this table: Read the rest of this entry >>

Categories: BI & Warehousing

DSS and BI

Dylan Wan - Fri, 2007-11-30 14:09

I found a very old book, called Decision Support Systems: An Organizational Perspective, in a library last weekend. It was written by Peter Keen,an author of several popular books, which help many business managersand users understand the value of information technology. His DSS bookdraw my attention because he is also the author of my textbook Network in Actions.

More...The DSS book uses a very typical and conventional categorization system which puts the IT systems into three types:

Transactional System, Structure Decision system, and Decision Support System.

These categories are created based on the classification ofdecisions into structured, unstructured, and partially structureddecision. His focus is the 3rd category, DSS. Peter believes that a DSSshould assist in solving the semi-structured problems. A DSS shouldsupport, not replace, the managers.

I feel that the above is a very good framework to view the role ofan analytics apps. A BI analytics application should be a DSS solution.However, BI analytics apps can do much more then just a decisionsupport system. BI may help the structured decision making.

BI is not just a collection of reports. The design of a BI analyticsapps needs to consider what are the business decision need to make andwhat kind of information is helpful for making the decision.

Categories: BI & Warehousing

Data Warehouse Project Lifecycle

Dylan Wan - Fri, 2007-11-02 01:39

Here is the typical lifecycle for data warehouse deployment project:

0. Project Scoping and Planning

Project Triangle - Scope, Time and Resource.

  • Determine the scope of the project - what you would like toaccomplish? This can be defined by questions to be answered. The numberof logical star and number of the OLTP sources
  • Time - What is the target date for the system to be available to the users
  • Resource - What is our budget? What is the role and profile requirement of the resources needed to make this happen.

1. Requirement

  • What are the business questions? How does the answers of these questions can change the business decision or trigger actions.
  • What are the role of the users? How often do they use the system?Do they do any interactive reporting or just view the defined reportsin guided navigation?
  • How do you measure? What are the metrics?

2. Front-End Design

  • The front end design needs for both interactive analysis and the designed analytics workflow.
  • How does the user interact with the system?
  • What are their analysis process?

3. Warehouse Schema Design

  • Dimensional modeling - define the dimensions and fact and define the grain of each star schema.
  • Define the physical schema - depending on the technology decision.If you use the relational tecknology, design the database tables

4. OLTP to data warehouse mapping

  • Logical mapping - table to table and column to column mapping. Also define the transformation rules
  • You may need to perform OLTP data profiling. How often the data changes? What are the data distribution?
  • ETL Design -include data staging and the detail ETL process flow.

5. Implementation

  • Create the warehouse and ETL staging schema
  • Develop the ETL programs
  • Create the logical to physical mapping in the repository
  • Build the end user dashboard and reports

6. Deployment

  • Install the Analytics reporting and the ETL tools.
  • Specific Setup and Configuration for OLTP, ETL, and data warehouse.
  • Sizing of the system and database
  • Performance Tuning and Optimization

7. Management and Maintenance of the system

  • Ongoing support of the end-users, including security, training, and enhancing the system.
  • You need to monitor the growth of the data.

Categories: BI & Warehousing

KDD 2007

Marcos Campos - Sun, 2007-08-12 08:34
For the next couple of days I am going to be attending the KDD (Knowledge Discovery in Databases) 2007 conference (conference website) along with some other Oracle colleagues. KDD is one of the primary conferences on data mining. This year it will take place in San Jose, CA, from August 12 to 15.Oracle is a Gold sponsor for the event and will have a large presence at the conference. Among other Marcos
Categories: BI & Warehousing

On the Road and Upcoming Talks

Marcos Campos - Mon, 2007-07-09 20:51
This week I am going to be in San Francisco. I have been invited to give a talk at the San Francisco Bay ACM Data Mining SIG on Wednesday. The title of the talk is In-Database Analytics: A Disruptive Technology. Here is a link with information on the talk.On Friday morning, I am presenting at the ST Seminar at Oracle's headquarter. The title of that talk is In-Database Mining: The I in BI. If Marcos
Categories: BI & Warehousing

Way Cooler: PCA and Visualization - Linear Algebra in the Oracle Database 2

Marcos Campos - Mon, 2007-06-04 12:05
This post shows how to implement Principal Components Analysis (PCA) with the UTL_NLA package. It covers some of the uses of PCA for data reduction and visualization with a series of examples. It also provides details on how to build attribute maps and chromaticity diagrams, two powerful visualization techniques.This is the second post in a series on how to do Linear Algebra in the Oracle Marcos
Categories: BI & Warehousing


Subscribe to Oracle FAQ aggregator - BI & Warehousing