Rittman Mead Consulting
Multiple OBIEE Environments
For any large OBIEE project the normal set-up would involve having a development machine, a test machine, Production machines and some form of disaster recovery. These normally need to be on separate physical machines, mainly for access rights reason and of course the DR machines need to be in a separate building (hopefully in a separate city!)
Did you know that you can have multiple OBIEE environments, but without buying new boxes?
Why do I need multiple Environments?I am not suggesting that development and Production are on the same box, but there are situations that demand environments, such as:
Multiple Projects - The success of many a good OBIEE project will often lead to other departments in the organisation wanting in on the action. Rather than build a whole new development, UAT and Production box you can just add more services to the existing one.
Clustering - although you often cluster for performance, you should also cluster for availability. The normal process is cluster over two or more boxes, but you can also cluster on the same box across users.
Development Cycles – Sometimes you need to create and test a version of your config (rpd /webcat) for a particular release, but carry on developing for the next release. This calls for multiple development environments.
Integration Testing - You can create your repository, but will it work in UAT and production, particularly if they have SSO and your dev environment does not? I prefer to have a pre-UAT environment in place for Developer testing, or internal testing teams to use.
Sandbox - The last thing you want is for all developers to use the same master development repository to implement new models or methods. Get the developer to work on their own copy, or new one, in a separate area to prove that it works.
Worldwide Development – I am not a big fan of MUD. It will work in certain situations, but you normally find that someone hogs the lock on the master repository. The main issue is new business models. One way around this is to have a master repository and have developers around the world use a copy to build their sections, then merge in their changes when they are tested.
Production Support – In a controlled environment the developers do not have access to production (I normally set the production rpd to read only just in case!)
Demo Site – To help your users understand what is possible you can install the sample sites and give all your potential users access.
Training Site – To support training you often need to build a separate environment. You may not want the hassle of whole box to do this on.
How Do I Create Multiple OBIEE Environments?Linux
On a Linux box you can install multiple environments into individul user accounts. There is no need to use vitualisation.
When you install a new OBIEE into a separate user you need to make sure that OC4J is not running. Other than that it is a normal simple installation.
After install you have to update all the ports in the system, making sure that you have the correct settings for BI Server, Presentation server, Javahost and graph server. The most important thing is to make sure you do not create spaghetti! Keep a central document on the environments and all their ports.
You can then update the cluster settings as normal, and put the webcat into a shared area for clustering.
We have created a script that does a silent installation and updates all the ports numbers.
Windows
Now there’s another story! My advice is stick to Unix or Linux for now. In theory you can create multiple services to run but I havn’t tried it yet. The simple solution would be to use virtualisation.
If you give it a go let me know how you get on.
Thoughts on Change Data Capture
In little over a month I will be in Las Vegas speaking at Collaborate 10. There is a lot of BI / DW talks this year and for the first time with BIWA Training Days branding. Rittman Mead will be there at the conference giving talks on each of the conference days. If you are at the conference (or even just on vacation there) then come and say ‘Hi’ to Stewart, Venkat, Mark and myself.
My talk will be about Realtime Data Warehousing – it is an overview of reasons, techniques and pitfalls, but I do cover a lot of material in that hour. Of course, Change Data Capture (CDC) will be a major part of the talk; Oracle has so many options here including their recently acquired GoldenGate product set. As always, the slides will be here on the Rittman Mead site soon after I speak.
My colleague, Stewart Bryson has also had some recent thoughts about change data capture over on the TDWI group at LinkedIn.com (group membership needed); he was quite preceptive (and on the money, in my opinion) with his comment “I would hesitate to let technical limitations dictate user requirements. In today’s BI/DW market, there are very few technical limitations that cannot be solved one way or another.”
One of points I will make in my Realtime DW talk, and perhaps I need a few more slides to do it justice, is the need to profile the change you capture on the source system. Often there is a lot of “noise” that looks like change but you have no real interest in it at the data warehouse. Not all systems are “well behaved”; I have seen systems that always update a record even if nothing has changed and even systems that update each column as separate statement with its own commit. Of course, even systems that don’t have those vices can still have columns that have no DW significance being updated and see those changes being filtered out on the data warehouse after we had already done a lot of work (processing, network bandwidth and the like) to get the data there.
The more I do this kind of work I feel there is a need to switch CDC on on the live source for a while and see the typical patten of change that occurs in a day, week, period whatever and then make decisions on how to handle this defensively downstream. Do we need to exclude certain columns that are just “noise”? What will be the impact of multiple, rapidly-occurring commits on how we handle SCD-2 dimensions? Of course we can predict what will see and come up with a proposed solution but the real source often has a few surprises up its sleeve – once a customer gave me a sequence of order statuses that an order passed through in its life-cycle except that on the actual source system the order sequence was not the same as their documentation and that would impact our reporting.
Oracle BI EE 10.1.3.4.1 – Solutions – Puzzle 4
The 4th puzzle in this series was a reasonably simple one which basically demonstrates a capability that allows end users to bypass the security applied in the BI Server layer. There are 3 possible solutions for this
Solution 1: Using Evaluate
EVALUATE was introduced in the 10.1.3.3.1 version of BI EE, that allows end users to call database functions directly. This feature has a lot of uses and actually is widely used. One such use case (others might term this as a bug) is its ability to call table columns that are not in the repository, by using native database functions. For example, the report shown below is a very simple one where only dimensional attributes CHANNEL_CLASS and CHANNEL_TOTAL exist in the report
The SQL for this report is give below
select distinct T4167.CHANNEL_TOTAL as c1,
T4167.CHANNEL_CLASS as c2
from
CHANNELS T4167
order by c1, c2
In our case CHANNEL_DESC column is completely not available for reporting as that is blocked by Security in the presentation layer as shown below
To bypass this, in one of the columns of the above report, use an EVALUATE function shown below
EVALUATE(’DECODE(%1,”Dummy”,NULL,CHANNEL_DESC)’ AS CHAR(10),Dim.CHANNEL_CLASS)
This will basically bypass the security of the BI Server and will provide a means of looking at the CHANNEL_DESC column directly. The report and the SQL are given below
select distinct T4167.CHANNEL_TOTAL as c1,
T4167.CHANNEL_CLASS as c2,
DECODE(T4167.CHANNEL_CLASS,'Dummy',NULL,CHANNEL_DESC) as c3
from
CHANNELS T4167
order by c1, c2, c3
So far so good. Now, lets try adding a measure to this report. You will notice that this will start producing an OCI error.
The reason is since CHANNEL_DESC was not part of the report directly, BI Server did not include that as part of the Select or the Group By clause. The wrong SQL is given below
select T4167.CHANNEL_TOTAL as c1,
T4167.CHANNEL_CLASS as c2,
DECODE(T4167.CHANNEL_CLASS,'Dummy',NULL,CHANNEL_DESC) as c3,
sum(1) as c4
from
CHANNELS T4167
group by T4167.CHANNEL_CLASS, T4167.CHANNEL_TOTAL
order by c1, c2, c3
Now, to make this work even when a fact attribute is included, the only option is to somehow push this column inside an Aggregate function like SUM, MAX etc since we cannot make the BI Server to generate the Group By (for the secured column). This requires a non-secure dimensional attribute at the same grain as the secured column. In our case, both CHANNEL_CLASS and CHANNEL_DESC have one to one relation and hence i will use that itself to demonstrate. The idea is to encapsulate the EVALUATE expression inside a string aggregate function like MAX etc as shown below
MAX(EVALUATE(’DECODE(%1,”Dummy”,NULL,CHANNEL_DESC)’ AS CHAR(10),Dim.CHANNEL_CLASS))
select T4167.CHANNEL_TOTAL as c1,
T4167.CHANNEL_CLASS as c2,
max(DECODE(T4167.CHANNEL_CLASS,'Dummy',NULL,CHANNEL_DESC)) as c3,
sum(1) as c4
from
CHANNELS T4167
group by T4167.CHANNEL_CLASS, T4167.CHANNEL_TOTAL
order by c1, c2
How do we disable this ability completely as this might be considered as a security bug? Ideally i would like to have a privilege in Answers, that can basically stop the use of EVALUATE functions thereby providing us with the capability of controlling who has access to this. But since this is not currently available, the easiest approach is to make sure that all your query columns are pushed into a sub-query. There are a multiple ways we can push all the columns to a sub-query. I will list them below
1. Using Logical Columns
2. Using Level Based Measures
3. Using SELECT based tables
There are other techniques as well. But for the sake of keeping this brief, i will show how all columns can be pushed into Sub-Queries using SELECT tables (instead of the normal tables obtained through Import). The idea is to use a simple table based on SELECT as shown below
instead of the normal imported table. Then we cannot use EVALUATE as the EVALUATE function can be pushed only to the sub-query. For example, the SQL for the same report above, using SELECT table instead of normal table is given below
select distinct T5419.CHANNEL_TOTAL as c1,
T5419.CHANNEL_CLASS as c2
from
(SELECT
CHANNEL_CLASS,
CHANNEL_CLASS_ID,
CHANNEL_ID,
CHANNEL_TOTAL,
CHANNEL_TOTAL_ID
FROM
CHANNELS) T5419
order by c1, c2
As you see, whatever function we apply in the front-end will always be pushed only to the outer query (inner sub-query will always remain the same which is for the SELECT table). Hence other than the columns in the Select sub-Query, EVALUATE cannot get external columns residing in the actual table (CHANNEL_DESC for example). If we try using the same EVALUATE function we will get an error. But this approach has potential performance issues since for every query, depending on the database, predicates might not get pushed from outer query to inner sub-query(within the optimizer) thereby causing performance issues.
People who answered this correctly: Craig, Anu
Solution 2: Using BYPASS_NQS authentication
This is not something that is normally used. But this is another important example where when the authentication model gets changed, the entire BI EE security can be bypassed. This security model allows any user to login to BI EE. But only database users will be allowed to report out of the databases(login to BI EE with the same username/password as the database). When this is done, any security that is applied at the column level will be bypassed and hence everyone can see the secured column data as well
Solution 3: Impersonation & Proxy Authentication
For this solution to work, one needs to have the privilege to do proxy authentication. Also, one cannot call this exactly as a security bypass since the user requires the capability to proxy in as another user. But there are cases when logged in as a single user, to look at the actual report values (during report development) we might want to login as Administrator which will essentially bypass all the security that is applied at the column level in BI Administrator. For more details on Proxy Authentication refer my blog post here.
The 5th Puzzle in this series to follow later this week.
Inside the Oracle BI Server Part 3 : BI Server In-Memory Joins
In the previous two postings in this series, I looked at the architecture of the Oracle BI Server, and how it processes incoming queries from Oracle BI Answers. In the latter article I touched on the concept of BI Server in-memory joins, and in this article I want to expand on this topic and look at just what goes on when the BI Server is called upon to combine data from multiple sources.
When the BI Server executes a query plan, it handles the data in four separate stages:
- Firstly, filters and functions are applied to the data from each data source
- Then, the data from these data sources are aggregated as required
- Then they are joined together (or “stitched” together), and
- Then, any calculations and/or aggregations that are applied across data sources are applied
In simple OBIEE environments, data used by a request will come from a single database, and therefore any joins that need to be performed by the BI Server will automatically be “pushed down” to the underlying database. In the cases though where more than one physical database is being used to provide data for a query, this join will instead need to be performed by the BI Server “in memory”. This ability to “federate” data sources, and therefore produce reports and analysis that span multiple data sources, but present the data to users as if it was a single database, is one of the key unique features of OBIEE and sets it apart from tools like Discoverer which are really restricted to reporting against single data sources.
So given this capability, how does it work under the covers? When does the BI Server perform a join in-memory, and when does it get done at the underlying database level? Where can we see what is happening, and can we predict what method the BI Server will use when performing a join? Finally, what algorithm does the BI Server use when performing these joins, and how does it use memory and disk when during the process?
To illustrate how the process works, there are a number of join scenarios that we need to consider. Some relate to joining fact and dimension tables together, and others relate to joining fact tables that share conforming dimensions, or hold conforming data sets of differing granularity.
Joining Fact and Dimension Tables Together
The BI Server semantic layer requires you to organize your business model and mapping layer into a star schema. This star schema may have one or more logical dimension tables, that join to one or more logical fact tables. The logical fact tables typically have conforming dimensions, so that you can create requests that span multiple fact tables and multiple dimension tables.
Taking for the moment joins between fact and dimension tables, depending on how the underlying physical or logical table source joins are set up in the semantic model, these may be either inner joins, left outer joins, right outer joins or full outer joins. The simple example to consider is a business model that is mapped to a single physical database, so that all logical table sources point to the same underlying data source, as shown in the screenshot below:
In this case, if we issued a request against this business model that required data from a dimension table and a fact table, the BI Server would push the join between logical table sources down to the underlying database, a single SQL query would be generated and the execution plan from a level 5 query log entry would look like this:
-------------------- Execution plan: RqList <<2105>> [for database 3023:2820:orcl3,44] PRODUCTS.PROD_SUBCATEGORY_DESC as c1 GB [for database 3023:2820,44], sum(SALES.QUANTITY_SOLD by [ PRODUCTS.PROD_SUBCATEGORY_DESC] ) as c2 GB [for database 3023:2820,44] Child Nodes (RqJoinSpec): <<2136>> [for database 3023:2820:orcl3,44] PRODUCTS T2874 SALES T2911 DetailFilter: PRODUCTS.PROD_ID = SALES.PROD_ID [for database 0:0] GroupBy: [ PRODUCTS.PROD_SUBCATEGORY_DESC] [for database 3023:2820,44] OrderBy: c1 asc [for database 3023:2820,44]
The same would apply to a left outer join between table sources in the same database, a right outer join or a full outer join. The BI Server doesn’t do any work here except to issue a single SQL query, and you can see just the one “RqList” (request list) in the execution plan, indicating again that the BI Server thinks it only needs to put together one query to satisfy the request.
If, however, one of the logical dimension tables had its logical table source re-pointed to a separate physical database, as shown in the screenshot below, the BI Server would now have to do the join itself, as it can’t be pushed down to the underlying database (as there are now two of them).
In this case, two SQL queries would be issued, one against each of the two physical databases, and the BI Server would do the join in-memory (or to disk, I’ll elaborate on this later on). The corresponding logical execution plan from a level 5 log file would now look like this:
-------------------- Execution plan: RqBreakFilter <<2465>>[1] [for database 0:0,0] RqList <<2466>> [for database 0:0,0] D1.c2 as c1 [for database 3023:2500,44], sum(D1.c5 by [ D1.c2] at_distinct [ D1.c2, D1.c3] ) as c2 [for database 0:0,0] Child Nodes (RqJoinSpec): <<2478>> [for database 0:0,0] ( RqList <<2482>> [for database 0:0,0] D902.c1 as c2 GB [for database 3023:2500,44], D901.c2 as c3 [for database 3023:132,44], D901.c3 as c5 [for database 3023:132,44] Child Nodes (RqJoinSpec): <<2490>> [for database 0:0,0] ( RqList <<2495>> [for database 3023:132:orcl,44] SALES.PROD_ID as c2 [for database 3023:132,44], sum(SALES.QUANTITY_SOLD by [ SALES.PROD_ID] ) as c3 [for database 3023:132,44] Child Nodes (RqJoinSpec): <<2504>> [for database 3023:132:orcl,44] SALES T211 GroupBy: [ SALES.PROD_ID] [for database 3023:132,44] OrderBy: c2 asc [for database 3023:132,44] ) as D901 InnerJoin <<2492>> On D901.c2 = D902.c2; actual join vectors: [ 0 ] = [ 1 ] ( RqList <<2517>> [for database 3023:2500:orcl2,44] PRODUCTS.PROD_SUBCATEGORY_DESC as c1 GB [for database 3023:2500,44], PRODUCTS.PROD_ID as c2 [for database 3023:2500,44] Child Nodes (RqJoinSpec): <<2523>> [for database 3023:2500:orcl2,44] PRODUCTS T2502 OrderBy: c2 asc [for database 3023:2500,44] ) as D902 OrderBy: c2, c3 [for database 0:0,0] ) as D1 OrderBy: c1 asc [for database 0:0,0]
Notice the “InnerJoin <<2492>> On D901.c2 = D902.c2; actual join vectors: [ 0 ] = [ 1 ]“ that is in the middle of the execution plan, between the two main Rqlists – this tells you that the BI Server is doing the join, as it would only appear here if it couldn’t be pushed down to the underlying database. You might also find references to LeftOuterJoin, RightOuterJoin and FullOuterJoin here, depending on how the join between the tables is defined in the physical or logical table source joins in your semantic layer.
Joining Facts with Conforming Dimensions Together
Another situation occurs when you are joining fact tables together that share conforming dimensions. A simple example of this is where you create a request that requires data from two or more fact tables that share conforming dimensions, such as those shown in the screenshot below:
As requests such as these can potentially lead to “fan trap” issues (explained in this blog post), the BI Server knows that it has to generate two logical queries and join, or “stitch” them together to avoid the fan trap. If both fact tables are sourced from the same physical database, and this database supports subquery factoring (the “WITH” clause that you see in Oracle 10gR2/11g SQL statements) then it will generate the following execution plan, which has a FullOuterStitchJoin between the two inner RqList blocks:
RqBreakFilter <<3571>>[3] [for database 0:0,0]
RqList <<3462>> [for database 3023:2820:orcl3,46]
D1.c1 as c1 GB [for database 3023:2820,46],
D2.c1 as c2 GB [for database 3023:2820,46],
case when D1.c2 is not null then D1.c2 when D2.c2 is not null then D2.c2 end as c3 GB [for database 3023:2820,46]
Child Nodes (RqJoinSpec): <<3567>> [for database 3023:2820:orcl3,46]
(
RqList <<3474>> [for database 3023:2820:orcl3,46]
sum(COSTS.UNIT_COST by [ PRODUCTS.PROD_SUBCATEGORY_DESC] ) as c1 GB [for database 3023:2820,46],
PRODUCTS.PROD_SUBCATEGORY_DESC as c2 GB [for database 3023:2820,46]
Child Nodes (RqJoinSpec): <<3507>> [for database 3023:2820:orcl3,46]
PRODUCTS T2874
COSTS T2830
DetailFilter: COSTS.PROD_ID = PRODUCTS.PROD_ID [for database 0:0]
GroupBy: [ PRODUCTS.PROD_SUBCATEGORY_DESC] [for database 3023:2820,46]
) as D1 FullOuterStitchJoin <<3565>> On D1.c2 = D2.c2
(
RqList <<3511>> [for database 3023:2820:orcl3,46]
sum(SALES.AMOUNT_SOLD by [ PRODUCTS.PROD_SUBCATEGORY_DESC] ) as c1 GB [for database 3023:2820,46],
PRODUCTS.PROD_SUBCATEGORY_DESC as c2 GB [for database 3023:2820,46]
Child Nodes (RqJoinSpec): <<3544>> [for database 3023:2820:orcl3,46]
PRODUCTS T2874
SALES T2911
DetailFilter: PRODUCTS.PROD_ID = SALES.PROD_ID [for database 0:0]
GroupBy: [ PRODUCTS.PROD_SUBCATEGORY_DESC] [for database 3023:2820,46]
) as D2
OrderBy: c3 asc [for database 3023:2820,46]
The BI Server Navigator then generates a single SQL statement off of this execution plan, which queries both fact tables using subquery factoring, and then brings the results together in the main body of the statement:
-------------------- Sending query to database named orcl3 (id: <<3462>>):
WITH
SAWITH0 AS (select sum(T2830.UNIT_COST) as c1,
T2874.PROD_SUBCATEGORY_DESC as c2
from
PRODUCTS T2874,
COSTS T2830
where ( T2830.PROD_ID = T2874.PROD_ID )
group by T2874.PROD_SUBCATEGORY_DESC),
SAWITH1 AS (select sum(T2911.AMOUNT_SOLD) as c1,
T2874.PROD_SUBCATEGORY_DESC as c2
from
PRODUCTS T2874,
SALES T2911
where ( T2874.PROD_ID = T2911.PROD_ID )
group by T2874.PROD_SUBCATEGORY_DESC)
select distinct SAWITH0.c1 as c1,
SAWITH1.c1 as c2,
case when SAWITH0.c2 is not null then SAWITH0.c2 when SAWITH1.c2 is not null then SAWITH1.c2 end as c3
from
SAWITH0 full outer join SAWITH1 On SAWITH0.c2 = SAWITH1.c2
order by c3
If the physical database doesn’t support subquery factoring, such as Oracle Database 10gR1 or higher, then the BI Server generates a slightly different execution plan, again with a FullOuterStitchJoin, like this:
-------------------- Execution plan:
RqBreakFilter <<3115>>[3] [for database 0:0,0]
RqList <<3006>> [for database 0:0,0]
D903.c1 as c1 GB [for database 3023:2820,44],
D903.c2 as c2 GB [for database 3023:2820,44],
case when D903.c3 is not null then D903.c3 when D903.c4 is not null then D903.c4 end as c3 GB [for database 3023:2820,44]
Child Nodes (RqJoinSpec): <<3117>> [for database 0:0,0]
(
RqList <<3160>> [for database 0:0,0]
D901.c1 as c1 GB [for database 3023:2820,44],
D902.c1 as c2 GB [for database 3023:2820,44],
D901.c2 as c3 [for database 3023:2820,44],
D902.c2 as c4 [for database 3023:2820,44]
Child Nodes (RqJoinSpec): <<3163>> [for database 0:0,0]
(
RqList <<3018>> [for database 3023:2820:orcl3,44]
sum(COSTS.UNIT_COST by [ PRODUCTS.PROD_SUBCATEGORY_DESC] ) as c1 GB [for database 3023:2820,44],
PRODUCTS.PROD_SUBCATEGORY_DESC as c2 GB [for database 3023:2820,44]
Child Nodes (RqJoinSpec): <<3051>> [for database 3023:2820:orcl3,44]
PRODUCTS T2874
COSTS T2830
DetailFilter: COSTS.PROD_ID = PRODUCTS.PROD_ID [for database 0:0]
GroupBy: [ PRODUCTS.PROD_SUBCATEGORY_DESC] [for database 3023:2820,44]
OrderBy: c2 asc [for database 3023:2820,44]
) as D901 FullOuterStitchJoin <<3109>> On D901.c2 = D902.c2; actual join vectors: [ 1 ] = [ 1 ]
(
RqList <<3055>> [for database 3023:2820:orcl3,44]
sum(SALES.AMOUNT_SOLD by [ PRODUCTS.PROD_SUBCATEGORY_DESC] ) as c1 GB [for database 3023:2820,44],
PRODUCTS.PROD_SUBCATEGORY_DESC as c2 GB [for database 3023:2820,44]
Child Nodes (RqJoinSpec): <<3088>> [for database 3023:2820:orcl3,44]
PRODUCTS T2874
SALES T2911
DetailFilter: PRODUCTS.PROD_ID = SALES.PROD_ID [for database 0:0]
GroupBy: [ PRODUCTS.PROD_SUBCATEGORY_DESC] [for database 3023:2820,44]
OrderBy: c2 asc [for database 3023:2820,44]
) as D902
) as D903
OrderBy: c3 asc [for database 0:0,0]
This is then resolved for this database into two separate SQL statements, which then joined “in-memory” together by the BI Server.
-------------------- Sending query to database named orcl3 (id: <<3018>>):
select sum(T2830.UNIT_COST) as c1,
T2874.PROD_SUBCATEGORY_DESC as c2
from
PRODUCTS T2874,
COSTS T2830
where ( T2830.PROD_ID = T2874.PROD_ID )
group by T2874.PROD_SUBCATEGORY_DESC
order by c2
+++Administrator:2a0000:2a0005:----2010/02/28 15:05:31
-------------------- Sending query to database named orcl3 (id: <<3055>>):
select sum(T2911.AMOUNT_SOLD) as c1,
T2874.PROD_SUBCATEGORY_DESC as c2
from
PRODUCTS T2874,
SALES T2911
where ( T2874.PROD_ID = T2911.PROD_ID )
group by T2874.PROD_SUBCATEGORY_DESC
order by c2
Joining Table Sources within a Logical Fact
Another situation is a fact table may have more than one logical table source, because individual measures are sourced from different data sources or perhaps measures may be mapped in at differing levels of granularity (this blog post describes such a scenario). In this case, again the BI Server will initially try and push the join down to the underlying database, something that may be possible if a single physical database is used and we can use a technique like subquery factoring; more likely though it will require the BI Server to issue two or more physical SQL statements and then bring the results back together again using a FullOuterStitchJoin.
-------------------- Execution plan:
RqList <<7829>> [for database 0:0,0]
D1.c1 as c1 [for database 0:0,0],
D1.c2 as c2 [for database 0:0,0],
D1.c3 as c3 [for database 0:0,0],
D1.c4 as c4 [for database 3023:4210,44]
Child Nodes (RqJoinSpec): <<7842>> [for database 0:0,0]
(
RqList <<7809>> [for database 0:0,0]
D1.c1 as c1 [for database 0:0,0],
D1.c2 as c2 [for database 0:0,0],
D1.c3 as c3 [for database 0:0,0],
D1.c4 as c4 [for database 3023:4210,44],
D1.c5 as c5 [for database 0:0,0]
Child Nodes (RqJoinSpec): <<7824>> [for database 0:0,0]
(
RqBreakFilter <<7808>>[1,2,5] [for database 0:0,0]
RqList <<7604>> [for database 0:0,0]
case when D903.c1 is not null then D903.c1 when D903.c2 is not null then D903.c2 end as c1 GB [for database 0:0,0],
case when D903.c3 is not null then D903.c3 when D903.c4 is not null then D903.c4 end as c2 GB [for database 0:0,0],
D903.c5 as c3 GB [for database 0:0,0],
D903.c6 as c4 GB [for database 3023:4210,44],
case when D903.c7 is not null then D903.c7 when D903.c8 is not null then D903.c8 end as c5 GB [for database 0:0,0]
Child Nodes (RqJoinSpec): <<7844>> [for database 0:0,0]
(
RqList <<7915>> [for database 0:0,0]
D901.c1 as c1 [for database 0:0,0],
D902.c1 as c2 [for database 3023:4210,44],
D902.c2 as c3 [for database 3023:4210,44],
D901.c2 as c4 [for database 0:0,0],
D901.c3 as c5 GB [for database 0:0,0],
D902.c3 as c6 GB [for database 3023:4210,44],
D901.c4 as c7 [for database 0:0,0],
D902.c4 as c8 [for database 3023:4210,44]
Child Nodes (RqJoinSpec): <<7918>> [for database 0:0,0]
(
RqList <<7851>> [for database 0:0,0]
D1.c2 as c1 [for database 0:0,0],
D1.c3 as c2 [for database 0:0,0],
D1.c1 as c3 GB [for database 0:0,0],
D1.c4 as c4 [for database 0:0,0]
Child Nodes (RqJoinSpec): <<7854>> [for database 0:0,0]
(
RqBreakFilter <<7687>>[2,3] [for database 0:0,0]
RqList <<8040>> [for database 0:0,0]
D1.c1 as c1 [for database 0:0,0],
D1.c2 as c2 [for database 0:0,0],
D1.c3 as c3 [for database 0:0,0],
D1.c4 as c4 [for database 0:0,0]
Child Nodes (RqJoinSpec): <<8058>> [for database 0:0,0]
(
RqList <<7972>> [for database 3023:4483:Quotas,2]
sum(QUANTITY_QUOTAS.QUOTA by [ CATEGORY.CATEGORY, MONTHS.MONTH_MON_YYYY] ) as c1 [for database 3023:4483,2],
MONTHS.MONTH_MON_YYYY as c2 [for database 3023:4483,2],
CATEGORY.CATEGORY as c3 [for database 3023:4483,2],
MONTHS.MONTH_YYYYMM as c4 [for database 3023:4483,2]
Child Nodes (RqJoinSpec): <<7682>> [for database 3023:4483:Quotas,2]
CATEGORY T4486
MONTHS T4488
QUANTITY_QUOTAS T4492
DetailFilter: CATEGORY.CATEGORY = QUANTITY_QUOTAS.CATEGORY and MONTHS.MONTH_YYYYMM = QUANTITY_QUOTAS.MONTH_YYYYMM [for database 0:0]
GroupBy: [ CATEGORY.CATEGORY, MONTHS.MONTH_YYYYMM, MONTHS.MONTH_MON_YYYY] [for database 3023:4483,2]
) as D1
OrderBy: c2, c3 [for database 0:0,0]
) as D1
OrderBy: c1 asc, c2 asc [for database 0:0,0]
) as D901 FullOuterStitchJoin <<7800>> On D901.c1 =NullsEqual D902.c1 and D901.c2 =NullsEqual D902.c2; actual join vectors: [ 0 1 ] = [ 0 1 ]
(
RqList <<7880>> [for database 3023:4210:orcl4,44]
D2.c2 as c1 [for database 3023:4210,44],
D2.c3 as c2 [for database 3023:4210,44],
D2.c1 as c3 GB [for database 3023:4210,44],
D2.c4 as c4 [for database 3023:4210,44]
Child Nodes (RqJoinSpec): <<7883>> [for database 3023:4210:orcl4,44]
(
RqBreakFilter <<7760>>[2,3] [for database 3023:4210:orcl4,44]
RqList <<7989>> [for database 3023:4210:orcl4,44]
sum(ITEMS.QUANTITY by [ PRODUCT.CATEGORY, TIMES.MONTH_MON_YYYY] ) as c1 [for database 3023:4210,44],
TIMES.MONTH_MON_YYYY as c2 [for database 3023:4210,44],
PRODUCT.CATEGORY as c3 [for database 3023:4210,44],
TIMES.MONTH_YYYYMM as c4 [for database 3023:4210,44]
Child Nodes (RqJoinSpec): <<7755>> [for database 3023:4210:orcl4,44]
PRODUCT T4256
TIMES T4264
ITEMS T4239
ORDERS T4248
DetailFilter: ITEMS.ORDID = ORDERS.ORDID and ITEMS.PRODID = PRODUCT.PRODID and ORDERS.ORDERDATE = TIMES.DAY_ID [for database 0:0]
GroupBy: [ PRODUCT.CATEGORY, TIMES.MONTH_MON_YYYY, TIMES.MONTH_YYYYMM] [for database 3023:4210,44]
) as D2
OrderBy: c1 asc, c2 asc [for database 3023:4210,44]
) as D902
) as D903
OrderBy: c1, c2, c5 [for database 0:0,0]
) as D1
OrderBy: c5 asc, c2 asc, c4 asc [for database 0:0,0]
) as D1
Again, notice the FullOuterStitchJoin in the execution plan – this indicates that facts (as opposed to facts and dimensions) are being joined together.
This in turn leads to two separate SQL statements. The one against the “orcl” database is more complex because the results then need to be mapped to the aggregation level that the second source, “quotas”, comes in at:
-------------------- Sending query to database named Quotas (id: <<7972>>):
select sum(T4492."QUOTA") as c1,
T4488."MONTH_MON_YYYY" as c2,
T4486."CATEGORY" as c3,
T4488."MONTH_YYYYMM" as c4
from
"CATEGORY" T4486,
"MONTHS" T4488,
"QUANTITY_QUOTAS" T4492
where ( T4486."CATEGORY" = T4492."CATEGORY" and T4488."MONTH_YYYYMM" = T4492."MONTH_YYYYMM" )
group by T4486."CATEGORY", T4488."MONTH_YYYYMM", T4488."MONTH_MON_YYYY"
+++Administrator:2b0000:2b000a:----2010/02/24 17:18:51
-------------------- Sending query to database named orcl4 (id: <<7880>>):
select D2.c2 as c1,
D2.c3 as c2,
D2.c1 as c3,
D2.c4 as c4
from
(select D1.c1 as c1,
D1.c2 as c2,
D1.c3 as c3,
D1.c4 as c4
from
(select sum(T4239.QUANTITY) as c1,
T4264.MONTH_MON_YYYY as c2,
T4256.CATEGORY as c3,
T4264.MONTH_YYYYMM as c4,
ROW_NUMBER() OVER (PARTITION BY T4256.CATEGORY, T4264.MONTH_MON_YYYY ORDER BY T4256.CATEGORY ASC, T4264.MONTH_MON_YYYY ASC) as c5
from
PRODUCT T4256,
TIMES T4264,
ITEMS T4239,
ORDERS T4248
where ( T4239.ORDID = T4248.ORDID and T4239.PRODID = T4256.PRODID and T4248.ORDERDATE = T4264.DAY_ID )
group by T4256.CATEGORY, T4264.MONTH_MON_YYYY, T4264.MONTH_YYYYMM
) D1
where ( D1.c5 = 1 )
) D2
order by c1, c2
So, to summarize things so far:
- Where possible, the BI Server will try and generate a single SQL statement to resolve a request
- And if possible, any joins that are required between tables will be pushed down to the database
- If table data sources are located on separate physical databases, the BI Server will request the individual data source data blocks, and then join the results together in-memory using an inner, left outer, right outer or full outer join as appropriate
- If facts (or measures within a fact) are being joined together, the BI Server will need to generate one logical query per logical table source, and bring the data together with a full outer stitch join
- As mentioned above, if it’s possible to do this stitch join at the database level (using, for example, a WITH clause), it’ll do so
- Otherwise the BI Server will generate separate SQL statements and join the data together in-memory
When an in-memory BI Server join happens between two tables, it will bring back both sets of data from the two (or more) table sources and then perform a sort-merge join to bring the data together. If possible, it will push the sort back to the underlying database and just do the “merge” part of the join, and it’ll in all likelihood page some of the temporary data to TMP files in $ORACLEBIDATA/tmp depending on the load on the server, available memory and the number of concurrent queries that it is running. The NQSConfig.INI BI Server parameter VIRTUAL_TABLE_PAGE_SIZE determines the point at which temporary data is paged to disk, and on a Unix server you can experiment with increasing it from its default setting if you have lots of unused memory available (the docs suggest that this will probably not have much of a positive effect, though).
Fragmented Data Sources
Another variation on a join that the BI Server can do is a “union” between two queries. This is most common when you have fragmented data sources, such as the example below where part of the data in the sales table comes from one table, and part from another.
In this case, the logical execution plan will contain RqUnionAll between the inner RqList request lists, to show that the BI Server knows it needs to union all the two queries.
-------------------- Execution plan:
RqList <<7569>> [for database 3023:6594:orcl7,44]
D3.c2 as c1 GB [for database 3023:6594,44],
sum(D3.c3 by [ D3.c2] ) as c2 GB [for database 3023:6594,44]
Child Nodes (RqJoinSpec): <<7695>> [for database 3023:6594:orcl7,44]
(
RqList <<7613>> [for database 3023:6594:orcl7,44]
PRODUCTS.PROD_SUBCATEGORY_DESC as c2 [for database 3023:6594,44],
SALES_UPTO_2003.AMOUNT_SOLD as c3 [for database 3023:6594,44]
Child Nodes (RqJoinSpec): <<7617>> [for database 3023:6594:orcl7,44]
PRODUCTS T6596
SALES T6629
DetailFilter: PRODUCTS.PROD_ID = SALES_UPTO_2003.PROD_ID [for database 0:0]
RqUnion All <<7690>> [for database 3023:6594:orcl7,44]
RqList <<7668>> [for database 3023:6594:orcl7,44]
PRODUCTS.PROD_SUBCATEGORY_DESC as c2 [for database 3023:6594,44],
SALES_BEYOND_2003.AMOUNT_SOLD as c3 [for database 3023:6594,44]
Child Nodes (RqJoinSpec): <<7672>> [for database 3023:6594:orcl7,44]
PRODUCTS T6596
SALES T6637
DetailFilter: PRODUCTS.PROD_ID = SALES_BEYOND_2003.PROD_ID [for database 0:0]
) as D3
GroupBy: [ D3.c2] [for database 3023:6594,44]
OrderBy: c1 asc [for database 3023:6594,44]
Then, depending on whether the BI Server can resolve this using a single query or multiple queries against separate data source, either a single SQL statement like the one below will be issued, or separate statements will be issued and the BI Server will do the union all in memory.
select D3.c2 as c1,
sum(D3.c3) as c2
from
((select T6596.PROD_SUBCATEGORY_DESC as c2,
T6629.AMOUNT_SOLD as c3
from
PRODUCTS T6596,
SALES T6629 /* SALES_UPTO_2003 */
where ( T6596.PROD_ID = T6629.PROD_ID )
union all
select T6596.PROD_SUBCATEGORY_DESC as c2,
T6637.AMOUNT_SOLD as c3
from
PRODUCTS T6596,
SALES T6637 /* SALES_BEYOND_2003 */
where ( T6596.PROD_ID = T6637.PROD_ID ) )
) D3
group by D3.c2
order by c1
Driving Tables (Parameterized Nested Loop Joins)
I mentioned in the paragraph above that BI Server joins are typically done using the sort-merge algorithm. One variation on this though is when you set one of the two tables in a business model and mapping logical join to be a driving table, typically because you are federating fact and dimension tables and one table is much smaller than the other, as shown in the screenshot below.
The first thing to understand with driving tables is that they are regarded as a “hint” by the BI Server, and the BI Server may well choose to ignore the setting if it makes more sense to perform the join as normal (presumably, when both tables are relatively small). If the driving table instruction is followed, though, the BI Server will always do the join in-memory, even if both tables come from logical table sources pointing to the same physical database. In the execution plan shown below, you can see the InnerJoin (left drive) that indicates a parameterized nested loop join (PNLJ) will be required, and as the name suggests the BI Server will perform a nested loop join rather than the sort-merge join that it usually uses to join tables together.
-------------------- Execution plan:
RqBreakFilter <<8705>>[1] [for database 0:0,0]
RqList <<8972>> [for database 0:0,0]
D1.c2 as c1 [for database 3023:2500,44],
sum(D1.c5 by [ D1.c2] at_distinct [ D1.c2, D1.c3] ) as c2 [for database 0:0,0]
Child Nodes (RqJoinSpec): <<8984>> [for database 0:0,0]
(
RqList <<8463>> [for database 0:0,0]
D901.c1 as c2 GB [for database 3023:2500,44],
D902.c2 as c3 [for database 3023:5035,44],
D902.c3 as c5 [for database 3023:5035,44]
Child Nodes (RqJoinSpec): <<8707>> [for database 0:0,0]
(
RqList <<8757>> [for database 3023:2500:orcl2,44]
PRODUCTS.PROD_NAME as c1 GB [for database 3023:2500,44],
PRODUCTS.PROD_ID as c2 [for database 3023:2500,44]
Child Nodes (RqJoinSpec): <<8760>> [for database 3023:2500:orcl2,44]
PRODUCTS T2502
DetailFilter: PRODUCTS.PROD_NAME = '128MB Memory Card' or PRODUCTS.PROD_NAME = '3 1/2" Bulk diskettes, Box of 100' or PRODUCTS.PROD_NAME = '5MP Telephoto Digital Camera' or PRODUCTS.PROD_NAME = '64MB Memory Card' or PRODUCTS.PROD_NAME = 'Deluxe Mouse' or PRODUCTS.PROD_NAME = 'Envoy Ambassador' or PRODUCTS.PROD_NAME = 'Envoy External 8X CD-ROM' or PRODUCTS.PROD_NAME = 'Martial Arts Champions' or PRODUCTS.PROD_NAME = 'Model A3827H Black Image Cartridge' or PRODUCTS.PROD_NAME = 'Model C93822D Wireless Phone Battery' or PRODUCTS.PROD_NAME = 'Model CD13272 Tricolor Ink Cartridge' or PRODUCTS.PROD_NAME = 'PCMCIA modem/fax 28800 baud' or PRODUCTS.PROD_NAME = 'SIMM- 16MB PCMCIAII card' or PRODUCTS.PROD_NAME = 'Smash up Boxing' or PRODUCTS.PROD_NAME = 'Unix/Windows 1-user pack' [for database 0:0]
OrderBy: c2 asc [for database 3023:2500,44]
) as D901
InnerJoin (left drive) <<8806>> On D901.c2 = D902.c2; actual join vectors: [ 1 ] = [ 0 ]
(
RqList <<8790>> [for database 3023:5035:orcl5,44]
SALES.PROD_ID as c2 [for database 3023:5035,44],
sum(SALES.AMOUNT_SOLD by [ SALES.PROD_ID] ) as c3 [for database 3023:5035,44]
Child Nodes (RqJoinSpec): <<8793>> [for database 3023:5035:orcl5,44]
SALES T5126
DetailFilter: SALES.PROD_ID = ?1 or SALES.PROD_ID = ?2 or SALES.PROD_ID = ?3 or SALES.PROD_ID = ?4 or SALES.PROD_ID = ?5 or SALES.PROD_ID = ?6 or SALES.PROD_ID = ?7 or SALES.PROD_ID = ?8 or SALES.PROD_ID = ?9 or SALES.PROD_ID = ?10 or SALES.PROD_ID = ?11 or SALES.PROD_ID = ?12 or SALES.PROD_ID = ?13 or SALES.PROD_ID = ?14 or SALES.PROD_ID = ?15 or SALES.PROD_ID = ?16 or SALES.PROD_ID = ?17 or SALES.PROD_ID = ?18 or SALES.PROD_ID = ?19 or SALES.PROD_ID = ?20 [for database 0:0]
GroupBy: [ SALES.PROD_ID] [for database 3023:5035,44]
OrderBy: c2 asc [for database 3023:5035,44]
) as D902
OrderBy: c2, c3 [for database 0:0,0]
) as D1
OrderBy: c1 asc [for database 0:0,0]
Then then leads to the following parameterized SQL statements being issued, with the first statement representing the “driving” query, and the second the “probing” one against the larger table.
-------------------- Sending query to database named orcl2 (id: <<8757>>):
select T2502.PROD_NAME as c1,
T2502.PROD_ID as c2
from
PRODUCTS T2502
where ( T2502.PROD_NAME in ('128MB Memory Card', '3 1/2" Bulk diskettes, Box of 100', '5MP Telephoto Digital Camera', '64MB Memory Card', 'Deluxe Mouse', 'Envoy Ambassador', 'Envoy External 8X CD-ROM', 'Martial Arts Champions', 'Model A3827H Black Image Cartridge', 'Model C93822D Wireless Phone Battery', 'Model CD13272 Tricolor Ink Cartridge', 'PCMCIA modem/fax 28800 baud', 'SIMM- 16MB PCMCIAII card', 'Smash up Boxing', 'Unix/Windows 1-user pack') )
order by c2
+++Administrator:2c0000:2c000a:----2010/02/24 21:06:47
-------------------- Sending query to database named orcl5 (id: <<8790>>):
select T5126.PROD_ID as c2,
sum(T5126.AMOUNT_SOLD) as c3
from
SALES T5126
where ( T5126.PROD_ID in (:PARAM1, :PARAM2, :PARAM3, :PARAM4, :PARAM5, :PARAM6, :PARAM7, :PARAM8, :PARAM9, :PARAM10, :PARAM11, :PARAM12, :PARAM13, :PARAM14, :PARAM15, :PARAM16, :PARAM17, :PARAM18, :PARAM19, :PARAM20) )
group by T5126.PROD_ID
order by c2
In reality you rarely see driving table joins being used as there are much better solutions to bringing together small and large tables together – the main one being to co-locate the tables and then push the join down to the database, rather than bring both datasets together and have the BI Server join them in memory instead (this also applies to a lesser degree to all BI Server joins). But this could be a useful “quick fix” until such time as you can co-locate the data, and its useful to remember that these types of joins are always done by the BI Server due to the need to iterate through drive/probe operations.
Persist Connnection Pools
One final variation on BI Server execution plans and join types is when you set up a “persist connection pool”. Persist connection pools are typically used in two scenarios; firstly, where Oracle/Siebel Marketing is being used, and secondly, where the underlying physical database doesn’t handle large numbers of values in an IN-list. In this case, you can set up a second connection pool within a physical database and specify it as the persist connection pool, as shown in the screenshot below:
I’ve never encountered a persist connection pool “in the wild”, so to speak, but an example query log output from when one was used is shown below. In this instance, the first query was sent to a MS Analysis Services database, and a persist connection pool was used to materialize the in-list results into a database table which is then joined back to the ORDERS table in the final query, rather than have the BI Server do the join in-memory.
-------------------- Sending query to database named FoodMart (id: <<10980>>):
With
member [Measures].[YearAnc] as 'ancestor([Time].Currentmember,[Time].[Year]).name'
set [Q] as '{{[Time].[Year].members}}'
select
{[measures].[YearAnc]} on columns,
{[Q]} on rows
from [Sales]
-------------------- Sending query to database named SQLDB_Northwind (id: CreateTable TransGateway):
CREATE TABLE TTCH5C5DEL554110000020000003 ( column1 VARCHAR2(8) )
-------------------- Sending query to database named SQLDB_Northwind (id: <<11057>>):
select distinct TO_NUMBER(TO_CHAR(T1864.OrderDate, 'yyyy'), '9999') as c1
from
Orders T1864
where ( TO_NUMBER(TO_CHAR(T1864.OrderDate, 'yyyy'), '9999') in (select column1 from TTCH5C5DEL554110000020000003) )
Conclusions
So, there you have it. The join strategy of the BI Server, as is the case with functions and calculations, is to wherever possible push them down to the underlying database. If this can’t be done, because either the database version doesn’t support features like subquery factoring, or if the data for the request is being sourced from more than one physical databas, the BI Server will do the join itself, initially in-memory but usually with temporary data being paged to disk.
There are two main types of BI Server join; regular (inner, left outer, right outer and fullouter) joins for bringing together fact and dimension tables; and full outer stitch joins, for bringing together facts and measures. There are also variations for handling joins from very small tables to very large tables (driving tables, or parameterized nested loop joins), or when the physical database doesn’t support large in-lists, however these issues are usually better handled by co-locating data or upgrading the database.
Finally, even though the BI Server is pretty clever at doing these types of joins, you’re usually better trying to invest your time in physically bringing your data together into a data mart or data warehouse than spending too much time fine-tuning these joins, though a knowledge of how they work (and how to read a level 5 execution plan) can be useful if you have to understand, or tune, an existing system in-place. Of course the level 5 execution plan doesn’t really tell you anything you couldn’t determine by looking at the design of the RPD – there’s nothing that goes on beyond this that might change the execution plan for a certain set of data, unlike the Oracle database which changes the plan from database to database depending on the distribution and nature of the data – but its interesting to get a peek into the workings of the BI Server Navigator module.
OBIEE Content at ODTUG Kaleidoscope 2010
As well as organizing our own BI Forum in the UK, another event I’ve had a hand in is ODTUG Kaleidoscope 2010, which is due to run from June 27th – July 1st in Washington D.C. I’ve been the content lead for the BI, DW and Hyperion Reporting Tools stream, and if you were thinking of coming to the BI Forum but couldn’t make it over to the UK, this event has a similar level of OBIEE content and might be of interest to you.
Like the BI Forum, we wanted to focus on the technical aspects of OBIEE, and as the event is running Stateside we were able to get some of the key product managers from Oracle to come and present, who don’t usually make it to general user group events like this but were keen to contribute to our OBIEE focus. Here’s a taster for what’s on the agenda:
OBIEE 11g Integration with Oracle ADF Business Components
Palaniappan Chidambaram, Oracle Corporation
With Oracle Middleware getting smarter and robust, it’s time to update the data source of OBIEE from relational database to Fusion ADF Business Model Components (light-weight Java View Objects). This is an efficient way to add pervasive BI to the enterprise applications built using Fusion ADF. Learn the new OBIEE 11g and Fusion ADF integration with added metadata on security, UI hints, and much more.
I met Palanippan back at last year’s Open World, and he’s responsible for some of the OBIEE/ADF integration that’s coming with the new Fusion Release of Oracle Applications. Palanippan will be talking about something that will be of interest to both OBIEE, and ADF developers, and will be speaking about it from the perspective of someone responsible for its features and usage.
Best Practices for Performance, Scalability, and Reliability with Oracle BI Enterprise Edition
Mike Durran, Oracle Corporation, and
Oracle OBIEE Metadata Modeling Best Practices and Tips for Concurrent Development
Alan Fuller, Oracle Corporation
The initial setup and configuration of an Oracle BI system can reap benefits in terms of ongoing performance and reliability. Mike’s session describes the creation of a system that can scale to your enterprise from the initial install, configuration for optimum performance, ongoing monitoring, and troubleshooting tips.
In Alan’s session, a senior member of the OBIEE product management team will cover best practices for metadata modeling in OBIEE including: using the power of the enterprise semantic layer for performance tuning and query optimization, rapid development processes, managing frequently encountered stumbling blocks, and making it easy for end users. Also covered will be tips for concurrent development of metadata across multiple developers.
From speaking with past attendees of Kaleidoscope, a regular bit of feedback is that people want to hear “best practice” sessions on their technology area of interest. Mike and Alan are therefore going to give Oracle’s view on best practices for OBIEE performance, and RPD modeling, which will also give the audience the change to discuss their best practices and comment on the ones put forward by Oracle.
Web Services and Application Integration with Oracle Business Intelligence EE Suite
David Granholm, Oracle Corporation
A variety of techniques can be used to build novel applications which extend the core capabilities of the OBIEE Suite. Approaches include URL-based integration, Web services leveraging SOAP methods, portal and WebCenter integration, and custom applications built in Oracle’s Application Development Framework (ADF) using Oracle JDeveloper – Oracle’s main development tool for Java-based SOA applications. Each of these approaches and a few real-world examples will be discussed.
David is a really good speaker, and this session will complement Palanippan’s and extend the discussion to SOAP and web services. Again, something of interest for general application developers as well as OBIEE developers.
Oracle Business Intelligence Applications Essbase Integrator
Alaric Thomas, Oracle Corporation
Oracle has a number of powerful business intelligence technologies in its portfolio, and we are rapidly integrating these technologies to provide more value and lower TCO for customers. In this session, Alaric Thomas will discuss how the Oracle BI Applications – Essbase Integrator will bring together the capabilities of the Oracle BI Applications and Oracle Essbase to further leverage a common applications information model delivering value to both IT and end users.
I blogged on this presentation a while ago, and this will be an excellent opportunity to see how the integration of Essbase and Oracle BI Apps is taking place, using technology available now rather than being based on the Fusion BI Apps.
Oracle® Hyperion Smart View for Office, Fusion Edition
Toufic Wakim, Oracle Corporation
For those working with a financial application, Oracle Essbase or Oracle Business Intelligence Suite Enterprise Edition Plus, Oracle Hyperion Smart View for Office brings access to data and report templates and the ability to author reports in the Microsoft Office framework. This session gives an overview of this product and demonstrates new features. It also shows how to use it with Oracle Hyperion Planning, Oracle Hyperion Financial Management, Oracle Business Intelligence Suite Enterprise Edition Plus, and Oracle Essbase.
SmartView is supposed to replace the BI Office add-in for OBIEE, but in its current incarnation doesn’t work too well. Apparently a new version that is more suited to OBIEE will be released shortly, and Toufic hopefully will discuss and demonstrate this version at Kaleidoscope. If you’re interested in the future of MS Office integration with OBIEE, this will be a must-see session.
Apart from these sessions, there’s a couple from myself on OBIEE 11g new features (assuming I can get sign-off to present, as 11g is doubtful for release by the time of Kaleidoscope), and a whole bunch of OWB, DW and ODI sessions as well as content aimed at Hyperion users upgrading to OBIEE. Outside of the sessions we’ll be organizing a social event specifically for the BI community, and there’ll of course be other great Essbase, Hyperion, Oracle and development sessions running concurrently.
Registration is open now, and if you’re serious about OBIEE and based Stateside (or even outside the USA, and fancy a trip to DC), this is definitely the event to attend in 2010.
Inside the Oracle BI Server Part 2 : How Is A Query Processed?
In the first article on this series about the Oracle BI Server, I looked at the architecture and functions within this core part of the OBIEE product set. in this article, I want to look closer at what happens when a query (or “request”) comes into the BI Server, and how it translates it into the SQL, MDX, file and XML requests that then get passed to the underlying data sources.
In the previous article, I laid out a conceptual diagram of the BI Server and talked about how the Navigator turned incoming queries into one or more physical database queries. As a recap, here’s the architecture diagram again:
Now as we all know, the BI Server uses a three-layer metadata model that exposes one or more databases (or “subject areas”) for ODBC-compliant query tools to run queries against. Here’s a typical metadata model that takes a number of physical data sources, joins them together into a smaller number of business model and mapping models, and then presents them out to the query tool (usually, Oracle BI Answers) as a set of databases made up of relational tables, columns and joins.
Usually you access this metadata model using Oracle BI Answers, which presents you with an initial choice of subject areas (databases in ODBC terminology) and then displays the contents of one of them as a list of tables and columns (in 11g, you’ll be able to to include tables from multiple subject areas in queries as long as there are tables in common between them).
Other ODBC-compliant query tools, such as Microsoft Excel, Cognos or Business Objects, can access these subject areas and run queries against them just as if it was a regular database. Here’s Microsoft Excel 2007 building a query against the same subject area:
What Happens When the BI Server Handles a Query?
So just what happens then, when a query (or “request’) comes in from one of these sources, and needs to be processed in order to return results to the user? As you’re probably aware, the BI Server doesn’t itself hold data (except cached results from other queries, when this feature is enabled); instead, it translates the incoming “logical” query into one or more outgoing “physical” queries against the relevant data sources. As such, a logical model presented to users might be mapped to data in an Oracle data warehouse, an E-Business Suite application, some data in a Teradata data warehouse, some measures in an Essbase cube and even some data in an Excel spreadsheet. The BI server resolves this complexity by creating a simplified, star schema business model over these data sources so that the user can query it as if it’s a single source of data.
If you’re used to the Oracle database, you’ll probably know that it has various components that are used to resolve queries – the library cache, query rewrite, table and system statistics, etc – and both rule-based and cost-based optimizers that are used to generate a query plan. For most modern Oracle systems, a statistics-based cost-based optimizer (most famously documented by Jonathan Lewis in this book) is used to generate a number of potential execution plans (which can be displayed in a 10035 trace), with the lowest cost being chosen to run the query. Now whilst the equivalent process isn’t really documented for the BI Server, what it appears to do is largely follow a rule-based approach with a small amount of statistics being used (or not used, as I’ll mention in a moment). In essence, the following sources of metadata information are consulted when creating the query plan for the BI Server;
- The presentation (subject area) layer to business model layer mapping rules;
- The logical table sources for each of the business columns used in the request;
- The dimension level mappings for each of the logical table sources;
- The “Number of Elements at this Level” count for each dimension level (potentially the statistics bit, though anecdotally I’ve heard that these figures aren’t actually used by the BI Server);
- Whether caching is enabled, and if so, whether the query can be found in the cache;
- What physical features are enabled for the particular source database for each column (and whether they are relational, multi-dimensional, file, XML or whatever)
- Specific rules for generating time-series queries, binning etc, and
- Security settings and filters
As far as I can tell, there are no indexes, no statistics (apart from the dimension level statistics mentioned above) and no hints; there is however query rewrite and aggregates, as the BI Server allows aggregate tables to be defined which are then mapped in to specific levels in a dimension hierarchy. Cleverly, the back-end data source doesn’t even have to be an SQL database, and can in fact be a multi-dimensional database such as Essbase, Oracle OLAP or Microsoft Analysis Services, with the multi-dimensional dataset that they return converted into a row-based dataset that can be joined to other data coming in from a more traditional relational database.
“A Day in the Life of a Query”
A good way of looking at what Oracle has termed “A day in the life of a query”, is to take a look at some slides from a presentation that Oracle used regularly around the time of the introduction of Oracle BI EE. I’ll go through it slide by slide and add some interpretation from myself.
1. A query comes in from Answers or any other ODBC query tool, asking for one or more columns from a subject area. Overall, the function within the BI Server that deals with this is called Intelligent Request Generation, marked in yellow in the diagram below.
2. The query is then passed to the Logical Request Generation engine, marked in yellow in the diagram below. The request itself requires the Brand, Closed Revenue (ultimately held in the GL system), Service Requests (held in the CRM system) and Share of Revenue (a calculated, or derived, measure). As such it’s going to require multiple physical SQL queries and multi-pass calculations, all of which will be worked out by another part of the BI Server architecture, the Navigator.
3. Once the logical request has been generated but before its passed off to the Navigator, a check is made (if this feature is enabled) as to whether the logical request can be found in the cache. Cache Services will either do a fast, or more comprehensive match of the incoming request against those stored in the query cache, and if found, return the results from there rather than have the BI Server run physical SQL against the business model’s data sources.
For a more detailed look at what Cache Services does, the old Siebel Analytics Administration Tool documentation has a good flowchart that explains what goes on:
The key bit is the Cache Hit step. In general, a cache hit will occur if the following conditions are met:
- Caching is enabled (CACHE=Y in the NQSConfig.INI file);
- The WHERE clause in the logical SQL is semantically the same, or a logical subset of a cached statement;
- All of the columns in the SELECT list have to exist in the cached query, or they must be able to be calculated from them;
- It has equivalent join conditions, so that the resultant joined table of any incoming query has to be the same as (or a subset of) the cached results
- If DISTINCT is used, the cached copy has to use this attribute as well
- Aggregation levels have to be compatible, being either the same or more aggregated than the cached query
- No further aggregation (for example, RANK) can be used in the incoming query
- Any ORDER BY clause has to use columns that are also in the cached SELECT list
In addition, there are two NQSConfig.INI parameters that I think were added in the last few releases (as I can’t find them mentioned in the Siebel Analytics documentation) are USE_ADVANCED_HIT_DETECTION and MAX_SUBEXPR_SEARCH_DEPTH. The latter determines how many levels into an expression (for example, SUM(MAX(SIN(COS(TAN(ABS(TRUNC(PROFIT)))))))) that the cache hit detector will go in trying to get a match, whilst the former turns on some additional cache hit searches that you might want to enable if caching is important but not otherwise happening. Unfortunately the docs don’t really expand on what these additional searches are or the performance impact that they can introduce, so if anyone has any more information on this, I’d be glad to hear.
4. If the cache can’t provide the answer to the request, the request then gets passed to the Navigator. The Navigator handles the logical request “decision tree” and determines how complex the request is, what data sources (logical table sources) need to be used, whether there are any aggregates that can be used, and overall what is the best way to satisfy the request, based on how you’ve set up the presentation, business model and mapping, and physical layers in your RPD.
5. Within the Navigator, the Multi-Pass / Sub-Request Logic function analyzes the incoming request and works out the complexity of the query. It works out whether it requires multiple passes (for example, calculates the average of two aggregated measures), or whether the request is based on the results of another request (in other words, uses a sub-request). The BI Server then uses this information to work out the optimal way to gather the required data and do the calculations; in the example used in the slides, the revenue share calculation is based on the other two measures and is therefore considered “multi-pass”.
6. A measure used within the business model and mapping layer may be “fragmented”, which means that it is logically partitioned so that historic information, for examples, comes from a data warehouse whilst current information comes from an OLTP application. The Fragment Optimization Engine within the Navigator sits between the incoming request and the Execution Engine and where appropriate, transforms the base-level SQL into “fragmented” SQL against each of the data sources mapped into the fragmented measure. For more background information on fragmentation, check out this old blog post on the subject.
7. The final function within the Navigator is the Aggregate Navigator, which uses the logical table source mappings together (in theory) with the dimension level statistics to determine the most efficient table to fetch the data from (i.e. the table with the least number of records to successfully fulfil a request).
8. The Optimized Query Rewrites function within the BI Server then takes the query plan generated by the Navigator and rewrites it to use the features of the underlying database engines, adding RANK(OVER()) calculations if Oracle is being used, for example (referred to as “function shipping”) or just getting the raw data and having the BI Server do the calculations afterwards, if working with a database that doesn’t support analytic SQL functions. This part of the BI Server is also responsible for generating XML queries, or MDX queries for OLAP sources,
which are then sent to the underlying physical databases, in parallel, so that they can retrieve their relevant data sets.
9. Once the data is retrieved, the results combined together and any further calculations applied, the results are returned to the calling application via the ODBC interface, and also copied to the cache along with the logical SQL query if caching is enabled.
The BI Server’s knowledge of what each source database can support, in terms of SQL functions, is determined by the contents of the DBFeatures.INI configuration file which can in turn be over-ridden by the “Features” tab in the Database settings in the physical database model.
I think I’ve also noticed that, from release to release of OBIEE, the way that time-series queries, for example, get resolved into physical SQL queries changes over time, as Oracle get better at generating efficient SQL queries to resolve complex calculations. It’s also the case that currently, for Essbase data sources, very few of the functions used by the BI Server get function-shipped to their equivalent MDX functions, though this is meant to be improving in the forthcoming 11g release (and in the meantime, you can use EVALUATE and EVALUATE_AGGR to call MDX functions directly).
Level 5 Logging, and Logical Execution Plans
You can see what goes on when a complex, multi-pass request that requires multiple data sources is sent through from Answers and gets logged in the NQQuery.log file with level 5 logging enabled. The query requests “quantity” information that is held in an Oracle database, “quotas” that comes from an Excel spreadsheet, and “variance” which is derived from quantity minus quotas. Both columns need to be aggregated before the variance calculation can take place, and you can see from the logs the Navigator being used to resolve the query.
Starting off, this is the logical request coming through.
-------------------- Logical Request (before navigation):
RqList
Times.Month Name as c1 GB,
Quantity:[DAggr(Items.Quantity by [ Times.Month Name, Times.Month ID] )] as c2 GB,
Quota:[DAggr(Items.Quota by [ Times.Month Name, Times.Month ID] )] as c3 GB,
Quantity:[DAggr(Items.Quantity by [ Times.Month Name, Times.Month ID] )] - Quota:[DAggr(Items.Quota by [ Times.Month Name, Times.Month ID] )] as c4 GB,
Times.Month ID as c5 GB
OrderBy: c5 asc
Then the navigator breaks the query down, works out what sources, multi-pass calculations and aggregates can be used, and generates the logical query plan.
-------------------- Execution plan:
RqList <<993147>> [for database 0:0,0]
D1.c1 as c1 [for database 0:0,0],
D1.c2 as c2 [for database 3023:491167,44],
D1.c3 as c3 [for database 0:0,0],
D1.c4 as c4 [for database 0:0,0]
Child Nodes (RqJoinSpec): <<993160>> [for database 0:0,0]
(
RqList <<993129>> [for database 0:0,0]
D1.c1 as c1 [for database 0:0,0],
D1.c2 as c2 [for database 3023:491167,44],
D1.c3 as c3 [for database 0:0,0],
D1.c4 as c4 [for database 0:0,0],
D1.c5 as c5 [for database 0:0,0]
Child Nodes (RqJoinSpec): <<993144>> [for database 0:0,0]
(
RqBreakFilter <<993128>>[1,5] [for database 0:0,0]
RqList <<992997>> [for database 0:0,0]
case when D903.c1 is not null then D903.c1 when D903.c2 is not null then D903.c2 end as c1 GB [for database 0:0,0],
D903.c3 as c2 GB [for database 3023:491167,44],
D903.c4 as c3 GB [for database 0:0,0],
D903.c3 - D903.c4 as c4 GB [for database 0:0,0],
case when D903.c5 is not null then D903.c5 when D903.c6 is not null then D903.c6 end as c5 GB [for database 0:0,0]
Child Nodes (RqJoinSpec): <<993162>> [for database 0:0,0]
(
RqList <<993219>> [for database 0:0,0]
D902.c1 as c1 [for database 0:0,0],
D901.c1 as c2 [for database 3023:491167,44],
D901.c2 as c3 GB [for database 3023:491167,44],
D902.c2 as c4 GB [for database 0:0,0],
D902.c3 as c5 [for database 0:0,0],
D901.c3 as c6 [for database 3023:491167,44]
Child Nodes (RqJoinSpec): <<993222>> [for database 0:0,0]
(
RqList <<993168>> [for database 3023:491167:ORCL,44]
D1.c2 as c1 [for database 3023:491167,44],
D1.c1 as c2 GB [for database 3023:491167,44],
D1.c3 as c3 [for database 3023:491167,44]
Child Nodes (RqJoinSpec): <<993171>> [for database 3023:491167:ORCL,44]
(
RqBreakFilter <<993051>>[2] [for database 3023:491167:ORCL,44]
RqList <<993263>> [for database 3023:491167:ORCL,44]
sum(ITEMS.QUANTITY by [ TIMES.MONTH_MON_YYYY] ) as c1 [for database 3023:491167,44],
TIMES.MONTH_MON_YYYY as c2 [for database 3023:491167,44],
TIMES.MONTH_YYYYMM as c3 [for database 3023:491167,44]
Child Nodes (RqJoinSpec): <<993047>> [for database 3023:491167:ORCL,44]
TIMES T492004
ITEMS T491980
ORDERS T491989
DetailFilter: ITEMS.ORDID = ORDERS.ORDID and ORDERS.ORDERDATE = TIMES.DAY_ID [for database 0:0]
GroupBy: [ TIMES.MONTH_MON_YYYY, TIMES.MONTH_YYYYMM] [for database 3023:491167,44]
) as D1
OrderBy: c1 asc [for database 3023:491167,44]
) as D901 FullOuterStitchJoin <<993122>> On D901.c1 =NullsEqual D902.c1; actual join vectors: [ 0 ] = [ 0 ]
(
RqList <<993192>> [for database 0:0,0]
D2.c2 as c1 [for database 0:0,0],
D2.c1 as c2 GB [for database 0:0,0],
D2.c3 as c3 [for database 0:0,0]
Child Nodes (RqJoinSpec): <<993195>> [for database 0:0,0]
(
RqBreakFilter <<993093>>[2] [for database 0:0,0]
RqList <<993319>> [for database 0:0,0]
D1.c1 as c1 [for database 0:0,0],
D1.c2 as c2 [for database 0:0,0],
D1.c3 as c3 [for database 0:0,0]
Child Nodes (RqJoinSpec): <<993334>> [for database 0:0,0]
(
RqList <<993278>> [for database 3023:496360:Quotas,2]
sum(QUANTITY_QUOTAS.QUOTA by [ MONTHS.MONTH_MON_YYYY] ) as c1 [for database 3023:496360,2],
MONTHS.MONTH_MON_YYYY as c2 [for database 3023:496360,2],
MONTHS.MONTH_YYYYMM as c3 [for database 3023:496360,2]
Child Nodes (RqJoinSpec): <<993089>> [for database 3023:496360:Quotas,2]
MONTHS T496365
QUANTITY_QUOTAS T496369
DetailFilter: MONTHS.MONTH_YYYYMM = QUANTITY_QUOTAS.MONTH_YYYYMM [for database 0:0]
GroupBy: [ MONTHS.MONTH_YYYYMM, MONTHS.MONTH_MON_YYYY] [for database 3023:496360,2]
) as D1
OrderBy: c2 [for database 0:0,0]
) as D2
OrderBy: c1 asc [for database 0:0,0]
) as D902
) as D903
OrderBy: c1, c5 [for database 0:0,0]
) as D1
OrderBy: c5 asc [for database 0:0,0]
) as D1
Notice the “FullOuterStitchJoin” in the middle of the plan? We’ll look into this more in the next posting in this series. For now though, this logical query plan is then passed to the Optimized Query Rewrites and Execution Engine, which then generates in this case two physical SQL statements that are then passed back, and “stitch joined”, by the BI Server, before performing the post-aggregation calculation required for the variance measure.
-------------------- Sending query to database named ORCL (id: <<993168>>):
select D1.c2 as c1,
D1.c1 as c2,
D1.c3 as c3
from
(select D1.c1 as c1,
D1.c2 as c2,
D1.c3 as c3
from
(select sum(T491980.QUANTITY) as c1,
T492004.MONTH_MON_YYYY as c2,
T492004.MONTH_YYYYMM as c3,
ROW_NUMBER() OVER (PARTITION BY T492004.MONTH_MON_YYYY ORDER BY T492004.MONTH_MON_YYYY ASC) as c4
from
CUST_ORDER_HISTORY.TIMES T492004,
CUST_ORDER_HISTORY.ITEMS T491980,
CUST_ORDER_HISTORY.ORDERS T491989
where ( T491980.ORDID = T491989.ORDID and T491989.ORDERDATE = T492004.DAY_ID )
group by T492004.MONTH_MON_YYYY, T492004.MONTH_YYYYMM
) D1
where ( D1.c4 = 1 )
) D1
order by c1
+++Administrator:2b0000:2b000e:----2010/02/23 16:04:42
-------------------- Sending query to database named Quotas (id: <<993278>>):
select sum(T496369."QUOTA") as c1,
T496365."MONTH_MON_YYYY" as c2,
T496365."MONTH_YYYYMM" as c3
from
"MONTHS" T496365,
"QUANTITY_QUOTAS" T496369
where ( T496365."MONTH_YYYYMM" = T496369."MONTH_YYYYMM" )
group by T496365."MONTH_YYYYMM", T496365."MONTH_MON_YYYY"
Memory Usage and Paging Files
If you follow the BI Server at the process level during these steps, you’ll find that memory usage is largely determined at startup time by the size and complexity of the RPD thats attached online, and then goes up by around 50MB when the first query is executed. After that, memory usage tends to go up the more concurrent sessions that are run, and also when cross-database joins are performed. You’ll also find TMP files being created in $ORACLEBIDATA/tmp directory, which are used by the BI Server to hold temporary data as it pages out from memory, again typically when cross-database joins are used but also when it needs to perform additional aggregations that can’t be put into the physical SQL query.
These files can get fairly big (up to 2GB in some cases) and can be created even when a single data source is used, typically for grouping data or as we’ll see in the next posting, when joining data across fact tables. They are usually cleared down when the BI Server and Presentation Server are restarted, but bear in mind when creating complex calculations that they can get pretty I/O intensive on the BI Server hardware.
So that’s the basics in terms of how basic queries are processed by the BI Server, and how the various BI Server components and engines process the query as it goes through the various stages. Again, if anyone knows any more, please add it as a comment, but for now that’s it and I’ll be back in a few days with part 3, on BI Server In-Memory Joins.
Oracle BI EE 10.1.3.4.1 – Puzzlers – Puzzle 4 – Bypassing Security
On to the 4th puzzle in this Puzzle series. This is a very interesting Puzzle at least from the perspective of BI EE Security. We all know that BI EE provides comprehensive security within the repository. For example, the screenshot below shows that the column CHANNEL_DESC can be accessed only by the exec user.
Now when we log in as any user(users who do not belong to Administrators group) and open the report containing this secured column, we will either be getting an error or will be shown as NULL in reports depending on the PROJECT_INACCESSIBLE_COLUMN_AS_NULL property in the NQSConfig.ini. The question is how do we bypass the security and still show the CHANNEL_DESC column(with their values of course) in reports. I know this sounds a bit malicious and can even be read as a security hack, but this is currently possible in BI EE(in the solution i will let you know how to use a workaround to disable this though). The question or the Puzzle today is how do we achieve this. There are some potential use cases of this
1. If you do not have access to the repository but still want to look at certain security protected columns to validate certain reports
2. If you do not have a column in the repository at all (but exists in the database) and you still want to access it.
Remember this is not about enabling Direct Database Requests as in most cases that will always be disabled. For example, if you look at the report below, CHANNEL_DESC has become null for the user i have logged in as this user does not have access to this column(PROJECT_INACCESSIBLE_COLUMN_AS_NULL is set to YES in my case).
If you look at the SQL, you will notice that the CHANNEL_DESC column is not even pushed back to the database.
WITH
SAWITH0 AS (select sum(1) as c1,
T4167.CHANNEL_ID as c2
from
CHANNELS T4167
group by T4167.CHANNEL_ID)
select distinct SAWITH0.c2 as c1,
cast(NULL as VARCHAR ( 1 ) ) as c2,
SAWITH0.c1 as c3
from
SAWITH0
order by c1, c2
which is good and as expected. Now, the puzzle is to somehow bypass this security and display the CHANNEL_DESC column as shown below.
Remember, there are 2 pre-conditions to this
1. No Direct Database Requests
2. No changing the repository to add a new column in the presentation layer
Oracle BI EE 10.1.3.4.1 – Solutions – Puzzle 3
After almost a week of me giving out the Puzzle 3, there was hardly any interest for this one (just couple of odd replies requesting clarification of the Puzzle). Probably this is because this one has no direct practical usage and also there are lots of possibilities. But to me this is one very important Puzzle as in many cases when we are called in for repository tuning, the first question that we normally get in such situations is, why does BI EE generate such a big query when the same report can be solved by a very simple query. In such cases, we need to know where to look at and also understand what can cause BI EE to generate long SQLs. Remember, there is no theoretical limit to the length of the SQL generated (we can make it to generate as big a SQL as we want). The puzzle was meant primarily to know the possibilities of what can make BI EE to generate long SQLs. I always try to visualize a repository by looking at the SQL. That generally helps in doing further analysis on a pre-built repository.
Solution 1 – Conforming Dimensions:
This is probably the easiest and the most common reason why BI EE generates a lot of sub-queries. Always conforming dimensions should be used only when absolutely necessary as that will start generating sub-queries for every fact. For example, if you look at the repository below, it shows a very simple Business Model and Mapping layer containing one dimension with one Logical table source pointing to the physical CHANNELS table.It also contains 3 Facts each having a count metric (Mapped to 1 in the BMM layer for all the 3 columns)
There is also one more Logical Column that basically adds all the 3 columns together using a logical calculation
Now, when you generate a report using the CHANNEL_TOTAL and the logical calculated column, you will notice that BI EE will generate 3 sub-queries and then will bring them together as shown below
So the solution is you can create n number of conforming dimensions like this to make BI EE to produce n sub-queries thereby making the SQL very long. In this case there is no need for actually using conforming dimensions. The same SQL can actually be converted into a single SQL with all the counts (without the sub-queries). This basically demonstrates a bad use of Conforming Dimensions.
Solution 2 – Fragmentation:
This is another way of generating big SQLs. Same source can be made to appear as part of UNION ALL queries using Fragmentation. For example, if you look at the repository below
there are basically 3 logical table sources that contribute to the Fact Count. Each logical table source is modeled in a way such that all of them contribute to the Count and follow Parallel Fragmentation as shown below
And if you look at the SQL, you will notice that BI EE will fire 3 UNION ALLs to generate the count. You can make this query as big as you want by adding more and more logical table sources.
Solution 3 – Level Based Measures:
This is another possible solution where incorrect use of Level-Based aggregation can start generating pretty complex queries. In your queries, if you start noticing Partition By using ROW_NUMBER or SUM() OVER functions then that means level based measures are being used somewhere (not in all cases but in most of them). For example, lets look at the repository below
As you notice we basically have have 3 count columns each dependent on the other. Count1 is a normal measure assigned to a constant 1 and to the lowermost level in the Channel Dimension. Count 1 is a measure which is equal to measure Count(logically calculated) but assigned to the Channel Class level. Count3 is equal to measure Count2(logically calculated) but assigned to the Total level.
As you see, this basically demonstrates why logically calculated measures can have different level assignments than their base members. If you look at the SQL generated,
you will notice a number of sub-queries which will equal to the number of level assignments for each dependent measure.
In all the cases above, the queries were generated using a single Physical table and from just using 2 report attributes. Much more complex queries are possible using other methods but most of them will be a variation of the 3 listed above. Puzzle 4 to follow tomorrow.
Inside the Oracle BI Server Part 1 : The BI Server Architecture
The session that I’m giving at the BI Forum in Brighton in May is entitled “Inside the Oracle BI Server”, and I’m aiming to take a closer look at the architecture and functionality of this key OBIEE component. We’re all fairly aware of what the BI Server does at a high level, but I thought it’d be interesting to take a closer look at what the BI Server does, particularly when it parses queries and joins datasets together.
At a very high level, the main function of the BI Server is to process inbound SQL requests against against a virtual database model, build and execute one or more physical database queries, process the data and then return it to users. The BI Server is one part of the Oracle BI Enterprise Edition Plus product family, and presents itself to query tools as one or more databases in a simple relational (star schema) model, that can then point to a much more complex set of relational, multidimensional, file and XML data sources (and in 11g, ADF objects).
Taking the standard OBIEE architecture diagram, the BI Server sits in the middle of the OBIEE set of servers and provides the query capability, security, interfaces to data sources and calculation logic for OBIEE (all of this is based on the current, 10g set of products).
The BI Server communicates with the BI Presentation Server via ODBC, and then connects out to the various supported data sources through ODBC, OCI, XML/A, the Essbase Client API and other native protocols. A key function of the BI Server is to create a three-layer metadata model, stored in a file-based repository along with security settings, database passwords, BI Server settings, startup macros and variable definitions.
The BI Server Logical Components
Taking a look specifically at the BI Server, it has a number of logical components.
- The ODBC interface, that is used by Oracle BI Answers and other third-party tools to pass requests to the BI Server, and to receive the output from queries;
- The Logical Business Model, the three-layer metadata model that describes the data available for queries;
- The Intelligent Request Generator, a module responsible for taking the incoming queries and turning them into physical queries against the connected data source, which is made up of several sub-components including:
- The Navigator, probably the most important part of the BI Server, and the part that takes the incoming query, compares it against cached answers, navigates the logical model and generates the physical queries that will best return the data required for the query
- Within the Navigator, there are modules for determining whether multiple physical queries are needed, whether stored aggregates can be used, and whether fragmented data sources can be used for partitioned measures;
- An Optimized Query Rewrite engine for handling aggregate navigation and fragments, and for translating to the correct physical SQL dialect, and
- An Execution Engine for firing off the queries to the relational, multi-dimensional, file and XML sources required to satisfy the query.
- Cache Services stores the results of previously run queries, matches incoming SQL against that used before and returns data from the cache rather than making the BI Server query the underlying databases again
In addition, various supporting technologies, modules and services provide the infrastructure for the BI Server, including:
- Data Source Adapters for Oracle, ODBC, SQL Server, DB/2, Teradata, file, XML and other sources;
- System and Performance Monitoring through JMX counters and other technologies;
- Security Services for setting up users and groups in the RPD, filters, subject area security, links to outside LDAP servers and custom authenticators;
- Query Governance, for placing limits on numbers of rows returned and length of query execution for users and groups;
- Load Balancing, and Session Management
Taking a Look at the BI Server Process
Now whilst the BI Server has many characteristics of a database, compared to running Oracle on Unix which exposes many of its components (SMON, PMON, MMON, LGWR etc) as separate processes, the BI Server is just a single executable that runs under the name NQSServer.exe (or just nqsserver under Unix). The screenshot below is a view of this service (along with sawserver.exe, the BI Presentation Server) as shown in the Windows Task Manager utility.
We’ll get on to memory usage in a future posting in this series, but in general the amount of memory taken up by the BI Server is initially determined by the size and complexity of the repository (RPD) that is running online, with further chunks taken up by concurrent sessions and then intermittent spikes of memory when in-memory (stitch) joins take place between data sources. The BI Server creates TMP (temporary) files in the $ORACLEBIDATA/tmp directory as data is further totalled and calculated, and as cross-database joins are paged to file.
If you take a closer look at the NQSServer.exe process using a tools such as Microsoft’s Process Explorer utility, you can see that it’s a multi-threaded server application:
You can see that the BI Server is a C++ application that uses the Microsoft Visual C++ runtime, whilst taking a look at one of the running threads shows the various DLLs that are being used:
Another Conceptual View of the BI Server
Another conceptual view of the BI Server architecture can be found in the old Siebel Analytics Administration Tool documentation, which shows the BI Server (or the Siebel Analytics Server as it was called then) having several layered components:
- The Security Model, presumably the users and groups in the RPD, plus the filters and subject area security in the repository;
- The Business Model, the three-layer metadata model;
- Aggregate Navigation, for rewriting queries to use mapped in aggregate tables;
- SQL Generation Engine and Multi-database Query Processing, presumably the bit that takes the database capabilities matrix and generates the correct physical SQL for the various data sources;
- The Computation Engine, for performing in-memory stitch joins, post-aggregation filters and functions, and sorting,
- Query cachiing
- The Metadata Repositories that can be connected to the BI Server (with one marked as “default”, and
- The various data sources, such as Oracle, DB/2, Informix and SQL Server
Conclusions
So the BI server has some of the characteristics of a BI tool (metadata model, connectivity to data sources, security etc) and some of a regular relational database (query processing, optimization, rewrite, aggregate navigation etc) but without OLTP database features such as transactions. Its primary job is to process incoming requests against this metadata model and translate them into the physical queries required to get the data from the underlying data sources, acting more as a query broker with no data being stored locally except that held in the cache. If you’re interested in a bit more history of the BI Server, including its origins as a search engine called the nQuire Query Server, take a look at this old blog post on the origins of Siebel Analytics and OBIEE where I’ve written up some of the original origins of the OBIEE product set.
The BI Server has one main configuration file, held at $ORACLEBI/server/config/NQSConfig.INI, which contains parameter settings in plain text. The full set of possible parameters are held in the Server Administrators’ Guide within the Oracle docs, and this method of holding parameter settings looks like it’ll be carried across to 11g, although the settings themselves will be maintained through Enterprise Manager rather than the Administration tool as is the case with 10g and earlier.
For now though, that’s it for architecture and components and in the next posting, I’ll be looking at how the BI Server, and in particular the Navigator, handles incoming requests.
Oracle 11g Pivot
One of the things that I often come across is the “up-dateable fact”, that is a fact that starts life “incomplete” and changes overtime. Examples include things such as support calls that start life as “open” then progress through “responded”, “resolved” and finally “closed”; statuses in the sales cycle such as ordered, paid, shipped; stock movements in a warehouse – goods received and dispatched. Of course the business, rightly, needs to measure the times between stages or the number or value of transactions at each stage.
As a principle, I hate the idea of having to update a fact. A fact has happened, it is not going to change. I suppose to be more accurate a “change” is a new event, a new fact, a new fact occurring at a different time. So how to model this? – well instinctively I would go for a table that is only inserted (preferably appended to – think set based!) containing whatever dimensions are needed (don’t forget the ‘when’ dimension) PLUS an ‘EVENT’ dimension (one row per expected status) and the measures (how many, how much etc). To report on this we need to rotate the table so that the events that belong to single item appear in the same row. Before Oracle 11g we would need to construct some SQL using a mix of case statements and analytic functions to rotate the data. But now we have a potentially better way the, Oracle 11g Pivot operator.
Here we define a set of dimensions for the row (similar to the dimensions in a Group BY clause), the aggregation operators for the pivoted measures – which of course could include MIN() or MAX() for the cases when want to pivot DATE types. We also need to define the dimensions we want to pivot by, and here we can actually choose multiple dimensions; this again is somewhat similar to the GROUP BY of traditional SQL. Remember though when we pivot we sometimes only expect to ‘aggregate’ a single row – if we want to pivot order date and dispatch date then we probably have just one of each!.
So how does it look? Well the Oracle 11g documentation describes the syntax and gives some examples – here I am showing a slightly more complicated case where we are pivoting by two dimensions, each with a known set of code values. This example is based on two of the examples in the Oracle 11g Data Warehousing Guide
SELECT * FROM ( SELECT product, channel, quarter, quantity_sold FROM sales_view ) PIVOT (SUM(quantity_sold) as SUMQ, SUM(amount_sold) as SUMS FOR (channel, quarter) IN ((5, '02') AS CATALOG_Q2, (4, '01') AS INTERNET_Q1, (4, '04') AS INTERNET_Q4, (2, '02') AS PARTNERS_Q2, (9, '03') AS TELE_Q3 ) );
The query returns a column for the product and for each of the specified pairs of channel and quarter a column for each measure. So we get columns for:
PRODUCT, CATALOG_Q2_SUMQ, CATALOG_Q2_SUMS, INTERNET_Q1_SUMQ, INTERNET_Q1_SUMS, INTERNET_Q4_SUMQ, INTERNET_Q4_SUMS, PARTNERS_Q2_SUMQ, INTERNET_Q4_SUMS, TELE_Q3_SUMQ, and TELE_Q3_SUMS
Note how the the measure name is concatenated to the alias in the in list.
As you can see we don’t need to specify each combination of channel and quarter – just the ones we want in our pivoted view. We also don’t use a GROUP BY clause – we specify the columns we want to see (both the dimensions and the aggregations) and Oracle implicitly groups by all of the columns not in aggregated functions.
In my example I used SELECT * to wrap the inline pivot, in practice I would explicitly select the columns and perhaps alias them to more meaningful names than the concatenated ones generated by Oracle. I would also expose the pivot as database view and thus access it from OWB or OBIEE where it appears to be just another table or view.
Another point to note is that you might see null values in the pivoted measures and these can be due to one of two reasons: the value stored for that combination of dimensions (in our case channel and quarter) is actually NULL, or that the combination does not exist. If you need to (and you may not need to) you can differentiate by using a COUNT measure; if the count is zero then the combination does not exist in the source table, if one or more then the source has NULLs stored for the combination.
We used a similar pivot view to the one above to monitor stock movements in a warehouse – in this case we needed to track individual batches of product from multiple potential suppliers, so in addition to the product dimension we had dimensional columns for batch id (a degenerate dimension) and supplier. The view was then exposed to OWB to allow us to include the aggregated result set in our ETL process – we needed to calculate some additional measures based on the difference between two of the pivoted columns. The Pivot operator greatly simplified our ETL for this fact – we could easily write an ETL process with a straight aggregation then pivot the results with CASE statements or DECODES or whatever – but that would have been less clear and also increased the number of “moving parts”.
We have had no problems with performance with our data set – 80 million rows pivoted on Exadata to just a few seconds. But it was not too slow on our non-exadata development machine either.
Atlanta BI Users Group Meeting
Rittman Mead is proud to announce our sponsorship of the February meeting of the Atlanta BI Users Group, a regional special interest group with involvement from IOUG, ODTUG the IOUG BIWA SIG, and the Georgia Oracle Users Group (GOUSER). I’d like to thank Greg Lancaster and the entire BI team at Cox Communications for hosting the event, which will take place at Cox Enterprises, Daytona B Conference Room, 6205 Peachtree Dunwoody Road, Atlanta, GA from 3:00 PM to 6:00 PM on February 24th. I’ll be giving a presentation similar to the one Mark Rittman and I gave at the RMOUG Training Days event described by Mark here.
With involvement from diverse groups, I wanted to make sure the presentation would be interesting from a lot of perspectives, so involving core database functionality like OWB seemed important. And this particular presentation is directed at DBAs and developers, so the non-BI representation from GOUSER might be interested in what’s going on.
If you’re new to OWB, don’t be alarmed by the focus on the newest release: 11gR2. I’ll explain in general what OWB is and how it’s positioned, moving into some of the new features that make this release the best OWB yet. Also speaking at the event will be Mitch Campbell, Director of Business Intelligence with Oracle, who will be talking about Oracle’s BI product roadmap. Following both talks, I’ll be sitting for a BI panel discussion with Mitch and Greg, to give responses to attendees questions, hopefully representing the perspectives of Oracle, their partners, and their customers.
The event is free, with snacks and refreshments provided, so hopefully we’ll see you there.
Oracle BI EE 10.1.3.4.1 – Puzzlers – Puzzle 3 – Negative Modeling
Now that i have blogged about the solution for Puzzles 1 and 2, its time for the next puzzle. This one requires you to actually build a very simple repository and show me how you can do negative modeling i.e a model that will produce the longest single SQL(basically a pretty big SQL), all on a single table. To expand further, lets assume that you have a single table in the physical database called CHANNELS(with 3 attributes). Now, you need to build a repository that will basically expose 2 columns in the presentation layer. When we create a report using these 2 columns, the SQL produced should be extremely long. I know this is not something that you would encounter anywhere but this will basically help you understand what can produce large SQLs in the database. There are 3 pre-conditions to the puzzle
1. No physical aliases can be used. Only one physical table(No select SQL) should exist in the physical layer. Else this will become very easy to solve.
2. Your entire SQL should be driven out of your Business Model in the repository.
3. Only 2 columns can be exposed in the presentation layer (i can be a bit flexible here. If you need to expose more columns, feel free to expose them if you think that will make the SQL to be long enough but ideally i would like only 2 to be exposed)
There is no business case/requirement that the RPD has to solve. All it has to do is to produce the longest SQL that you think is possible. Of course again, there are multiple possible solutions but all i need is a generic idea of how you would be implementing this.
A sample SQL (which is not that long but longer than the normal simple select) is given below of a simple report containing 2 columns
WITH
SAWITH0 AS (select D1.c1 as c1,
D1.c2 as c2,
D1.c3 as c3
from
(select T1776.CHANNEL_CLASS as c1,
T1776.CHANNEL_CLASS_ID as c2,
T1776.CHANNEL_TOTAL_ID as c3,
ROW_NUMBER() OVER (PARTITION BY T1776.CHANNEL_CLASS_ID ORDER BY T1776.CHANNEL_CLASS_ID ASC) as c4
from
CHANNELS T1776
) D1
where ( D1.c4 = 1 ) ),
SAWITH1 AS (select sum(1) as c1,
T1776.CHANNEL_TOTAL_ID as c2
from
CHANNELS T1776
group by T1776.CHANNEL_TOTAL_ID),
SAWITH2 AS (select D1.c1 as c1,
D1.c2 as c2,
D1.c3 as c3,
D1.c4 as c4
from
(select SAWITH0.c1 as c1,
SAWITH1.c1 as c2,
case when SAWITH1.c2 is not null then SAWITH1.c2 when SAWITH0.c3 is not null then SAWITH0.c3 end as c3,
SAWITH0.c2 as c4,
ROW_NUMBER() OVER (PARTITION BY SAWITH0.c1, SAWITH0.c2,
case when SAWITH1.c2 is not null then SAWITH1.c2
when SAWITH0.c3 is not null then SAWITH0.c3 end
ORDER BY SAWITH0.c1 ASC, SAWITH0.c2 ASC,
case when SAWITH1.c2 is not null
then SAWITH1.c2 when SAWITH0.c3 is not null
then SAWITH0.c3 end ASC) as c5
from
SAWITH0 full outer join SAWITH1 On SAWITH0.c3 = SAWITH1.c2
) D1
where ( D1.c5 = 1 ) )
select SAWITH2.c1 as c1,
SAWITH2.c2 as c2,
SAWITH2.c3 as c3,
SAWITH2.c4 as c4
from
SAWITH2
order by c1
Puzzle 4 to follow next which again will be in the lines of negative modeling.
Oracle BI EE 10.1.3.4.1 – Solutions – Puzzles 1 & 2
Well its been almost a week since i blogged the first 2 puzzles in this puzzle series. I guess it is time for me to blog about the solutions. I have been amazed by the response so far. Its been really positive, so more puzzles to come during this week and the next.
Puzzle 1 Solution1:
Well, this was a very easy puzzle and lot of people got it right. There are a couple of possible solutions. The most obvious and the easiest solution for this is to create a universal security group in the repository and then in the filters area assign the column level filters (vary the filter depending on the column) as shown below
I had to put this as a puzzle since this is one feature that i feel is the most under-utilized in BI EE. The drawback with this is the fact that this solution cannot be applied for Administrator user directly. But that is one user that is rarely exposed directly to end users.
People who got it right: Nicolas Gerard, Fiston and Harish Gopalan
Puzzle 1 Solution 2:
The other solution is to use a combination of multiple logical table sources in the dimension logical table. This is again another solution that directly affects the modeling we do in the Business Model and Mapping layer. Though it requires some work compared with solution 1, this can prove to be quite useful when the requirement is rephrased a bit like this. The report columns and the filters for the rephrased question is given below
“CHANNEL_CLASS & AMOUNT_SOLD” – Filter required is CHANNEL_CLASS = ‘Puzzle1 Test’
“CHANNEL_DESC & AMOUNT_SOLD” – Filter required is CHANNEL_DESC = ‘Puzzle Test2′
“CHANNEL_CLASS, CHANNEL_DESC & AMOUNT_SOLD” – Filter required is CHANNEL_DESC = ‘Puzzle Test3′ and CHANNEL_CLASS = ‘Puzzle Test4′
In such a case, solution 1 cannot be used. Only solution 2 can be used. Screenshots of the BMM is given below for this
Each LTS will have a separate filter. Ordering of the LTS is important to push the predicate filters properly.
People who got it right: Christian Berg, Nicholas Gerard and Ilmari Alto
Puzzle 2:
Puzzle 2 was a bit trickier as there are quite a few possible solutions. But lot of people answered it correctly.
Puzzle 2 Solution 1:
Everyone who answered this correctly, have used the solution 1. This basically alters 2 Database Features. They are given below. The idea is to basically enable both the options below
1. PERF_PREFER_MINIMAL_WITH_USAGE
2. PERF_PREFER_INTERNAL_STITCH_JOIN
Or the other option is to just switch between 10gR1 and 11g/10gR2 as the database in the database type (this basically does the same thing as above – enables both the options above)
The drawback with this solution is, altering the database features can start affecting other queries. WITH clause provides such an advantage and sometimes even in a normal model(without conforming dimensions), the queries might get pushed to the BI Server. I generally do not recommend altering the database features unless its told by Oracle support. For example there have been instances in older versions of BI EE where PERF_PREFER_INTERNAL_STITCH_JOIN was asked to be enabled/disabled by Oracle support (due to bypass some 10gR1/10gR2 database bug). I generally prefer a solution where everything is always pushed to the database but if due to some reason we want to enable the BI Server join then we should be in a position to do that. For example, there have been instances(couple of bugs in BI EE which even exists in 10.1.3.4.1) where BI EE generates wrong sub-totals when pushed to the database. In many cases, this happens when conforming dimension joins happen at the database and we start getting nulls in the results. To check whether sub-total query is wrong we might want to push everything (even the conforming dimension join to the BI Server). The following 2 solutions basically demonstrate that. One other potential issue is, if you have more than one fact table from the same database then, even those conforming dimensions will be pushed to the BI Server layer. The other drawback is, i can still make BI EE to generate the database joins. Want to know how? Well, lets enable the above 2 settings in the physical database features. And lets create a report. In the Advanced SQL of the report, enter the below logical SQL
SELECT A.CHANNEL_CLASS saw_0, A.QUANTITY_SOLD saw_1, B.UNIT_COST saw_2 FROM ( SELECT Channel.CHANNEL_CLASS, Sales.QUANTITY_SOLD FROM "SH - Conforming Dimension) A FULL OUTER JOIN ( SELECT Channel.CHANNEL_CLASS, Costs.UNIT_COST FROM "SH - Conforming Dimension") B ON A.CHANNEL_CLASS = B.CHANNEL_CLASS ORDER BY saw_0
If you look at the report now, you will notice that this join has actually been pushed into the database (even after disabling the WITH clause and STITCH join features)
But from a Puzzle standpoint, i was expecting any one of these solutions. So thanks for everyone who answered this correctly.
People who got it right: Vinod Jaganathan, Harish Gopalan, Somasekhar
Puzzle 2 Solution 2:
This involves adding more metadata to the repository and altering the BMM layer. But this provides you complete control on how the joins will be generated and you can be sure about the structure of the SQL Query. Basically the idea is to replicate the dimension and the fact table in 2 separate databases. For example, CHANNELS and SALES in one database & CHANNELS and COSTS in the other database as shown below
And your BMM dimension will have 2 sources (same CHANNELS source coming from 2 different Physical Databases as shown below
If you think logically, when we model the same set of tables in different databases, from a BI EE standpoint, these 2 are completely different. So, if you think from a SQL standpoint, the only way we can enforce the conforming dimension join in the database is by using Database links. Since we do not have them here, only way BI EE can join them is by stitching them in its memory. Remember, if you create aliases within the same physical database, even then BI EE will force the SQL to the database as, from a BI EE perspective, all the tables still reside in the same database. The issue with this approach is, it will become very difficult to manage the BMM layer especially when you have a physical source like BI Applications. But this is something to keep in mind while doing any Repository Modeling.
People Who got it right: Scott Powell (was close in mentioning the alias solution but i give the benefit of the doubt as this was the only comment suggested that was in line with this solution)
Puzzle 2 Solution 3:
This is something that is used rarely. Now that 11g is around the corner where everything will be based on View Objects etc, it becomes even more important to consider this. Understanding Logical SQL is very important as in many cases we would be using BI Server and some other front end instead of presentation services. The solution is to use a logical SQL that basically combines both the facts in separate sub-queries. This is not a straight forward solution. This requires introducing just a single CHANNELS table in another physical database (instead of all the tables,including the facts as described in the above solution) and creating a dummy Presentation Subject area containing the table.
Then use logical SQL method to push the join to the BI Server layer (introduce the new presentation layer object Channels)
A simple logical SQL that i used is given below
SELECT A.CHANNEL_CLASS saw_0, A.QUANTITY_SOLD saw_1, B.UNIT_COST saw_2 FROM ( SELECT Channel.CHANNEL_CLASS, Sales.QUANTITY_SOLD FROM "SH - Conforming Dimension" ) A FULL OUTER JOIN ( SELECT Channel.CHANNEL_CLASS FROM "SH - Conforming Dimension#1" ) B ON A.CHANNEL_CLASS = B.CHANNEL_CLASS FULL OUTER JOIN ( SELECT Channel.CHANNEL_CLASS, Costs.UNIT_COST FROM "SH - Conforming Dimension" ) C ON A.CHANNEL_CLASS = C.CHANNEL_CLASS ORDER BY saw_0
As you see i basically fired 3 separate sub-queries and then joined them together in the logical SQL. When you enter this in Answers, you will notice that the join is pushed to the BI Server irrespective of how you have modeled the repository. A sub-query join in BI Server means a join at the BI Server layer.
If you look at the SQL, you will notice that there are 3 SQLs that get fired simultaneously and all of them are joined in the BI Server memory. Not exactly the solution i was looking for but this pushes the join to the BI Server layer. This technique has a lot of drawbacks as well like you lose the drills in the front-end etc. But i generally always like the logical SQL approach not for implementation but for visualizing how your BMM layer has to be structured.
There is one more solution as which also uses an extra new physical database table. But i will leave that for now as it more or less follows what i have discussed in the above 2.
There can be other possible solutions as well. If you can think of any feel free to add them in the comments. As i have said this before BI Server is one of the best tools out there that can effectively model most reporting scenarios. There can be more than one possible way of arriving at a solution. Of course, there will be advantages and disadvantages associated with each approach. The idea of the Puzzle series is to basically highlight as many solutions along with their drawbacks etc.
OWB, RMOUG and ODTUG in Denver
I’m writing this on the evening following the Rocky Mountains Oracle User Group Training Days Conference in Denver, Colorado, where Stewart Bryson and I delivered our session on OWB11gR2 New Features for DBAs and Developers. We were both pleased with the turnout, managed to deliver five demos across the new code template functionality, and took some good questions from the audience at the end. Thanks to Peggy King and all the others at RMOUG for inviting us over, and here’s Stewart, up on stage and ready to go, just before starting our session:
This was our first time at RMOUG, probably the biggest and best regional user group conference in the States and very similar to our UK Oracle User Group conference in terms of speakers, technical coverage and friendliness of the speakers. This year RMOUG took the opportunity to broaden their coverage into areas such as Hyperion, SOA and business intelligence, so it was also good to see people such as Edward Roske here delivering a number of Essbase and Hyperion sessions. We took our usual “OWB11gR2 New Features” presentation and gave it a DBA and database developer angle, and whilst most of the audience were new to OWB hopefully the new ODI-derived functionality still made a bit of sense. If you’re interested, the paper and presentation can be downloaded from our articles page.
Now whilst Stewart has now gone back to Atlanta, I’ve stayed on as I’m taking part in my first ever ODTUG Board Meeting. I got elected to the board late last year and whilst I know most of the board members already (having been the BI&DW SIG co-chair for the last few years), it’ll be very interesting to take part and contribute to some of the user group decisions, and to see how we can improve and build on the BI content going forward. Certainly, the BI stream agenda for this year’s Kaleidoscope, due to be held in Washington D.C. in June, is about the best BI agenda I’ve seen for a long time (including lots of sessions by the Oracle PMs responsible for the 11g release), and I’m looking forward to working with the board to increase this coverage in the future. So for me, it’s another three days work going into the weekend, then I fly back to the UK on Sunday ready to deliver a course on Tuesday. Busy times, but all good fun.
Off to Denver
I’m sitting in the Atlanta airport waiting for my flight to Denver to go to the RMOUG Training Days, which I described here. It’s snowing (again) in Atlanta, but not very much this time, and I think my flight will take off. To reiterate about RMOUG, Mark and I will be speaking about new features in OWB 11gR2, but the focus will be functionality for DBA’s and database developers. If you come to the presentation, or see Mark or myself around the conference, feel free to say hello. We would enjoy talking with you about your current BI/DW project, discussing any issues you may be having, or anything that’s on your mind. Any recommendations on places to go in Denver would be greatly appreciated.
Announcing the Rittman Mead BI Forum 2010, May 19th – 21st 2010, Hotel Seattle Brighton UK
I’m very pleased to announce that registration is now open for the Rittman Mead BI Forum 2010, to be held at the Hotel Seattle, Brighton on May 19th – 21st 2010.
Like last year’s event, the 2010 BI Forum is focused on Oracle BI Enterprise Edition and the technologies that support it, such as Essbase, ODI EE and the Oracle Database. We have a number of the world’s top speakers on the subject, including names such as
- Gerard Braat (Oracle Corporation), on “Complex Modelling with OBIEE 11g”
- Antony Heljula (Peak Indicators), on “BI EE Architectures and Sizing”
- Venkatakrishnan J (Rittman Mead), on “Fusion Middleware 11g – ADF Business Components”
- Craig Stewart, (Oracle Corporation), on “ODI 11g: The New Generation of Data Integration”, and
- John Minkjan (Ciber), on “OBIEE Customizations: When You Are In The Kitchen, Learn to Cook”
For a full listing of all the sessions, check out the event website at http://www.rittmanmead.com/biforum2010
As well as general sessions on the 20th and 21st May, we also have a keynote by Phil Bates (OBIEE Architect, Oracle Corporation) at the event reception on the evening of the 19th May, and a special one-day OBIEE Masterclass earlier in the day by none other than Kurt Wolff, one of the original nQuire team and an expert on repository modeling and dashboard design.
The pricing for the event is very reasonable and we’ve also subsidized the cost of two nights in the event hotel, so that we can all stay in the same place over the three days. There’s also the opportunity to stay for an extra night before and after the event, if you want to arrive the night before for Kurt’s masterclass, or your flight back doesn’t leave until the Saturday.
As with last year, we’ve taken a decision to limit the number of attendees to 50, to maximize the opportunities for networking and to allow us to keep the focus on intermediate-to-advanced OBIEE topics. Last year’s attendees have already been able to register for the past week, but we’re now opening up registrations publically and there are around 25 places left. If you are interested in attending, check out the event website and register here, and hopefully we’ll see you in Brighton in May!
Oracle BI EE 10.1.3.4.1 – Puzzlers – Puzzle 2
I received some interesting replies through email as well as in the blog comments for the last Puzzle. I will blog about the solution for that and the solution for the 2nd puzzle together later this week. So far 3 people have answered and all are right which i will explain in my solution blog. Thanks to everyone who participated so far (Gerard Nico, Christian & Harish Gopalan). It is still open and you can still post your solution, if you haven’t already, until i blog the solutions later this week. On to the second Puzzle of the week today.
This is a very common but an interesting scenario which sometimes can act as the key in determining the performance of BI EE especially when you have a big repository with a lot of users accessing it simultaneously. As you all know BI EE avoids fan traps through the concept of conforming dimensions. Since conforming dimensions are used quite frequently, it is imperative that BI EE generates the right performing SQL or rather we model the repository in such a way that BI EE generates the correct SQL queries. Today’s puzzle is
“How do we push the conforming dimension join to the database and how do we push the conforming dimension join to the BI Server(how do we control this behavior)?”
For example, if you consider the repository below, it is a very simple model with Channels Dimension joining to 2 fact tables COSTS and SALES.
Now when someone creates a report containing both the measures and a dimension attribute, we want the conforming dimension join to be pushed into the database SQL itself as shown below
WITH
SAWITH0 AS (select sum(T1929.QUANTITY_SOLD) as c1,
T1776.CHANNEL_DESC as c2
from
CHANNELS T1776,
SALES T1929
where ( T1776.CHANNEL_ID = T1929.CHANNEL_ID )
group by T1776.CHANNEL_DESC),
SAWITH1 AS (select sum(T1784.UNIT_COST) as c1,
T1776.CHANNEL_DESC as c2
from
CHANNELS T1776,
COSTS T1784
where ( T1776.CHANNEL_ID = T1784.CHANNEL_ID )
group by T1776.CHANNEL_DESC)
select distinct case when SAWITH1.c2 is not null then SAWITH1.c2 when SAWITH0.c2 is not null then SAWITH0.c2 end as c1,
SAWITH0.c1 as c2,
SAWITH1.c1 as c3
from
SAWITH0 full outer join SAWITH1 On SAWITH0.c2 = SAWITH1.c2
order by c1
As you see in the above case, the query was completely fired back to the database. You can confirm that this is a conforming dimension join by looking at the full outer join clause and the entire SQL. Remember, it is not necessary in your case to generate the exact same SQL listed above. It is enough if you can demonstrate how the entire query can be pushed as a single query to the database i.e a modified form of the above SQL is acceptable as well.
The second part of the puzzle is how do we model the same repository above to achieve the conforming dimension join to happen in the BI Server memory as shown below
You can see that the conforming dimension join in this case happens in the BI Server layer. BI Server basically fires 2 queries separately and stitches them together in its own memory. In what ways can we control this behavior? There are more than one solution for this but again this is a little bit trickier than the Puzzle 1 i gave yesterday. Another important point to note is, in both the above cases, the output of the report should not change i.e both database based join and BI Server based join should produce the same results.
Some more interesting puzzles lined up for next week. Stay Tuned!!!
Oracle BI EE 10.1.3.4.1 – Puzzlers – Puzzle 1
If you had noticed the news last week, Oracle’s BI EE has achieved a leader status in the Gartner’s BI Magic Quadrant(i think 3rd year in a row). This can only mean one thing. More and more customers have started to implement BI EE and are happy with it as well. I have been planning to put together a small series of articles that are more like Repository Modeling puzzlers which should hopefully pique the interest of all the folks who are currently using BI EE. Repository Modeling is one of those areas that is least documented but it is the probably the biggest area that Oracle BI EE is extremely good at when compared with other reporting tools. And also it is sort of a subjective area where different people can have different techniques to achieve the same functionality. In the forthcoming articles (including this one), i would basically blog a small series of repository modeling puzzlers which everyone can participate in. All you have got to do is to, go through the blog entry, understand the question and come out with a RPD which can solve that particular use case. If you feel that the puzzle is very easy to achieve/solve, then you can put your solution as a comment to the blog entry. For every puzzler blog, i will come out with a solution blog where i will give the credit to the folks who have come out with the correct solution for that particular use case. If you have a RPD to send, do send them to venkat@rittmanmead.com.
We are hoping to do a similar sort of Puzzler series (with an interesting twist & completely different set of questions 
) in our BI Forum later this year. I will start with a very simple one today. But this is something that anyone rarely uses but that is extremely powerful. The puzzle is
“How do we make BI EE to generate different filters for every column(within a dimension) chosen from Answers?”
For example, lets take the SH Schema in the Oracle Database. Listed below are all the attributes of the CHANNEL dimension that have been exposed to Answers.
When someone creates a report containing CHANNEL_CLASS attribute and a measure, the SQL fired should automatically apply a filter on the CHANNEL_CLASS column. A sample SQL is shown below
select T1776.CHANNEL_CLASS as c1,
sum(T1929.AMOUNT_SOLD) as c2,
T1776.CHANNEL_CLASS_ID as c3
from
CHANNELS T1776,
SALES T1929
where ( T1776.CHANNEL_CLASS = 'Puzzle1 Test' and T1776.CHANNEL_ID = T1929.CHANNEL_ID )
group by T1776.CHANNEL_CLASS, T1776.CHANNEL_CLASS_ID
If you notice in the above SQL, there is a filter T1776.CHANNEL_CLASS = ‘Puzzle1 Test’ which gets automatically applied (without any filter in the Answers).
Now, when someone removes CHANNEL_CLASS and replaces it with CHANNEL_DESC column, then the SQL fired should have a CHANNEL_DESC (not the older filter that we applied above) specific filter as shown below
select T1776.CHANNEL_DESC as c1,
sum(T1929.AMOUNT_SOLD) as c2,
T1776.CHANNEL_ID as c3
from
CHANNELS T1776,
SALES T1929
where ( T1776.CHANNEL_DESC = 'Puzzle Test2' and T1776.CHANNEL_ID = T1929.CHANNEL_ID )
group by T1776.CHANNEL_DESC, T1776.CHANNEL_ID
If you notice, the above SQL has a CHANNEL_DESC specific filter. The question is how do you model your RPD to achieve this. Or if there is any other easy solution without going into modeling, feel free to put them in comments. This is a very simple puzzle(not exactly a puzzle but more of an in-built feature) but something that is extremely powerful as i had mentioned before. The next puzzle will be a little bit more challenging, i promise!!!
Data Warehouse Fault Tolerance Part 3: Restoring
Hopefully you’ve read the introduction, Part 1, and Part 2. Those posts detailed methods for building fault-tolerant ETL code, with a strong bias in favor of using Oracle Database features. Now I’ll drill into the backup and recovery aspect of data warehousing fault tolerance, and tackle the age-old question of whether to ARCHIVELOG or NOARCHIVELOG in a BI/DW environment.
When I engage with clients that have a data warehouse operating in NOARCHIVELOG mode, their usual reasoning for this decision is a perceived performance gain. This makes sense on the surface… because NOARCHIVELOG prevents the generation of all that unwanted and unneeded REDO, right?
Not exactly. There is misconception about what NOARCHIVELOG actually means, and hopefully, I can clear that up with a demonstration. I have a database in NOARCHIVELOG, and I’ll test to see whether my statements generate REDO:
SQL> SELECT log_mode
2 FROM v$database;
LOG_MODE
------------
NOARCHIVELOG
1 row selected.
Elapsed: 00:00:00.00
SQL>
SQL> CREATE TABLE target.sales
2 AS SELECT *
3 FROM sh.sales
4 WHERE 1=0;
Table created.
Elapsed: 00:00:00.59
SQL>
SQL> SET autotrace on statistics
SQL>
SQL> INSERT INTO target.sales
2 SELECT *
3 FROM sh.sales;
918843 rows created.
Elapsed: 00:00:02.92
Statistics
----------------------------------------------------------
1897 recursive calls
40779 db block gets
7062 consistent gets
1585 physical reads
38832896 redo size
742 bytes sent via SQL*Net to client
958 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
918843 rows processed
SQL>
SQL> ROLLBACK;
Rollback complete.
Elapsed: 00:00:01.32
SQL>
SQL> INSERT /*+ APPEND */ INTO target.sales
2 SELECT *
3 FROM sh.sales;
918843 rows created.
Elapsed: 00:00:06.00
Statistics
----------------------------------------------------------
1042 recursive calls
5581 db block gets
2874 consistent gets
1052 physical reads
92108 redo size
732 bytes sent via SQL*Net to client
975 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
918843 rows processed
SQL>
SQL> ROLLBACK;
Rollback complete.
Elapsed: 00:00:00.00
SQL>
The regular insert statement generated 38M of REDO in a NOARCHIVELOG database. Interesting. And the INSERT /*+ APPEND */ statement generated only 92K. Though it would appear that neither of these statements actually executed in NOLOGGING mode, the truth is that the APPEND statement did. All statements generate a little bit of REDO, because updates to the data dictionary are always logged.
So why do regular inserts generate REDO on a NOARCHIVELOG database? There is a myth in the Oracle world that NOARCHIVELOG means that no REDO is generated, but that is not the case. Choosing NOARCHIVELOG mode simply means that we are foregoing the option to use media recovery (restoring datafiles, rolling forward). Think about it: REDO is not simply for media recovery, it’s also for crash recovery. If all REDO generation was suspended, Oracle wouldn’t be able to open after a simple server crash. In NOARCHIVELOG mode, there are situations where we can suspend most of the REDO generated, and one of those situations involves using the INSERT /*+ APPEND */ statement. So why would the database allow these NOLOGGING operations? Because direct-path operations write blocks directly into datafiles, bypassing the buffer cache. We wouldn’t have to rely on the online REDO logs to recover those transactions, and so Oracle allows us to minimize the REDO generated.
So if you have your database in NOARCHIVELOG mode for performance reasons, but you are using ETL tools that don’t support true direct-path writes on Oracle (a lot of the third-party tools don’t), or you are using cursor-based, row-by-row load scenarios, the same amount of REDO is generated if the database was in ARCHIVELOG mode. The only thing gained from operating in this manner is the privilege of having to shut down the database whenever a backup is needed.
Perhaps another myth that gets perpetuated is that we can’t have the best of both worlds, but in fact we can. We can minimize the amount of REDO generated, we can operate in ARCHIVELOG mode, we can backup our database in online mode, and we would be able to restore from that backup. The solution: NOLOGGING tables and indexes. I’ll put the database in ARCHIVELOG mode, and rerun the test case above with one small change: I’ll change the table to be NOLOGGING:
SQL> startup mount
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1336960 bytes
Variable Size 343935360 bytes
Database Buffers 71303168 bytes
Redo Buffers 6094848 bytes
Database mounted.
SQL> alter database
2 archivelog;
Database altered.
SQL> alter database
2 open;
Database altered.
SQL> SELECT log_mode
2 FROM v$database;
LOG_MODE
------------
ARCHIVELOG
1 row selected.
Elapsed: 00:00:00.06
SQL>
SQL> ALTER TABLE target.sales
2 nologging;
Table altered.
Elapsed: 00:00:01.02
SQL>
SQL> SET autotrace on statistics
SQL>
SQL> INSERT INTO target.sales
2 SELECT *
3 FROM sh.sales;
918843 rows created.
Elapsed: 00:00:02.47
Statistics
----------------------------------------------------------
15560 recursive calls
33573 db block gets
13861 consistent gets
6260 physical reads
38289752 redo size
740 bytes sent via SQL*Net to client
958 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
154 sorts (memory)
0 sorts (disk)
918843 rows processed
SQL>
SQL> ROLLBACK;
Rollback complete.
Elapsed: 00:00:01.45
SQL>
SQL> INSERT /*+ APPEND */ INTO target.sales
2 SELECT *
3 FROM sh.sales;
918843 rows created.
Elapsed: 00:00:03.51
Statistics
----------------------------------------------------------
1 recursive calls
4628 db block gets
1718 consistent gets
59 physical reads
8072 redo size
732 bytes sent via SQL*Net to client
975 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
918843 rows processed
SQL>
SQL> ROLLBACK;
Rollback complete.
Elapsed: 00:00:00.03
SQL>
We get the exact same behavior with a NOLOGGING table in ARCHIVELOG mode than we did with NOARCHIVELOG mode. But is having the database in ARCHIVELOG mode of any value when all of our ETL processes are NOLOGGING? We can perform an online backup, but would we even be able to restore from that backup if we have transactions that executed as NOLOGGING?
The answer is “yes” and “yes”. We just need one small change to our backup strategy: a well-placed incremental backup.
To increase the performance of our incremental backup, we need to create a block change tracking file. The database keeps a list of all changed blocks so that RMAN will know exactly what to backup during an incremental:
SQL> alter database enable block change tracking 2 using file '/oracle/oradata/bidw1/change_blocks.bct'; Database altered. Elapsed: 00:00:02.16 SQL> select * from 2 v$block_change_tracking; STATUS | FILENAME | BYTES ------------ | ---------------------------------------- | ---------- ENABLED | /oracle/oradata/bidw1/change_blocks.bct | 11599872 1 row selected. Elapsed: 00:00:00.01 SQL>
We start by taking the initial incremental level 0 backup:
RMAN> backup incremental 2> level 0 database 3> plus archivelog; Starting backup at 11-FEB-10 current log archived using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=45 device type=DISK channel ORA_DISK_1: starting archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=18 RECID=47 STAMP=710646180 input archived log thread=1 sequence=19 RECID=48 STAMP=710646955 channel ORA_DISK_1: starting piece 1 at 11-FEB-10 channel ORA_DISK_1: finished piece 1 at 11-FEB-10 piece handle=/oracle/flash_recovery_area/BIDW1/backupset/2010_02_11/o1_mf_annnn_TAG20100211T015555_5q7bhw0c_.bkp tag=TAG20100211T015555 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02 Finished backup at 11-FEB-10 Starting backup at 11-FEB-10 using channel ORA_DISK_1 channel ORA_DISK_1: starting incremental level 0 datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001 name=/oracle/oradata/bidw1/system01.dbf input datafile file number=00002 name=/oracle/oradata/bidw1/sysaux01.dbf input datafile file number=00003 name=/oracle/oradata/bidw1/undotbs01.dbf input datafile file number=00004 name=/oracle/oradata/bidw1/users01.dbf input datafile file number=00005 name=/oracle/oradata/bidw1/example01.dbf input datafile file number=00007 name=/oracle/oradata/bidw1/target01.dbf input datafile file number=00006 name=/oracle/oradata/bidw1/tdrep01.dbf channel ORA_DISK_1: starting piece 1 at 11-FEB-10 channel ORA_DISK_1: finished piece 1 at 11-FEB-10 piece handle=/oracle/flash_recovery_area/BIDW1/backupset/2010_02_11/o1_mf_nnnd0_TAG20100211T015557_5q7bhz1o_.bkp tag=TAG20100211T015557 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:05:26 channel ORA_DISK_1: starting incremental level 0 datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current control file in backup set including current SPFILE in backup set channel ORA_DISK_1: starting piece 1 at 11-FEB-10 channel ORA_DISK_1: finished piece 1 at 11-FEB-10 piece handle=/oracle/flash_recovery_area/BIDW1/backupset/2010_02_11/o1_mf_ncsn0_TAG20100211T015557_5q7btbnf_.bkp tag=TAG20100211T015557 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:09 Finished backup at 11-FEB-10 Starting backup at 11-FEB-10 current log archived using channel ORA_DISK_1 channel ORA_DISK_1: starting archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=20 RECID=49 STAMP=710647302 channel ORA_DISK_1: starting piece 1 at 11-FEB-10 channel ORA_DISK_1: finished piece 1 at 11-FEB-10 piece handle=/oracle/flash_recovery_area/BIDW1/backupset/2010_02_11/o1_mf_annnn_TAG20100211T020143_5q7btr8x_.bkp tag=TAG20100211T020143 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 Finished backup at 11-FEB-10 RMAN>
Now I’ll load the SALES table with another INSERT /*+ APPEND */ to make sure we have a NOLOGGING operation since our last backup.
SQL> insert /*+ APPEND */
2 into target.sales
3 select * from
4 sh.sales;
918843 rows created.
Elapsed: 00:00:21.06
Statistics
----------------------------------------------------------
2780 recursive calls
6081 db block gets
2434 consistent gets
5442 physical reads
136036 redo size
1536 bytes sent via SQL*Net to client
1155 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
10 sorts (memory)
0 sorts (disk)
918843 rows processed
SQL> commit;
Commit complete.
Elapsed: 00:00:00.07
SQL>
This is the step in our process that requires a slight change to our backup and recovery strategy: we should get an incremental level 1 backup as soon as the load is complete. This will physically backup all blocks that have been affected by the load, and we wouldn’t need to logically apply the REDO logs that are missing the NOLOGGING operations. Since we have changed block tracking, this step will be extremely fast, and I recommend that the ETL process flow or main driving script execute the backup as the very last step in the batch load.
RMAN> backup incremental 2> level 1 database 3> plus archivelog; Starting backup at 11-FEB-10 current log archived using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=30 device type=DISK channel ORA_DISK_1: starting archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=18 RECID=47 STAMP=710646180 input archived log thread=1 sequence=19 RECID=48 STAMP=710646955 input archived log thread=1 sequence=20 RECID=49 STAMP=710647302 input archived log thread=1 sequence=21 RECID=50 STAMP=710648694 channel ORA_DISK_1: starting piece 1 at 11-FEB-10 channel ORA_DISK_1: finished piece 1 at 11-FEB-10 piece handle=/oracle/flash_recovery_area/BIDW1/backupset/2010_02_11/o1_mf_annnn_TAG20100211T022455_5q7d67t6_.bkp tag=TAG20100211T022455 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 11-FEB-10 Starting backup at 11-FEB-10 using channel ORA_DISK_1 channel ORA_DISK_1: starting incremental level 1 datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001 name=/oracle/oradata/bidw1/system01.dbf input datafile file number=00002 name=/oracle/oradata/bidw1/sysaux01.dbf input datafile file number=00003 name=/oracle/oradata/bidw1/undotbs01.dbf input datafile file number=00004 name=/oracle/oradata/bidw1/users01.dbf input datafile file number=00005 name=/oracle/oradata/bidw1/example01.dbf input datafile file number=00007 name=/oracle/oradata/bidw1/target01.dbf input datafile file number=00006 name=/oracle/oradata/bidw1/tdrep01.dbf channel ORA_DISK_1: starting piece 1 at 11-FEB-10 channel ORA_DISK_1: finished piece 1 at 11-FEB-10 piece handle=/oracle/flash_recovery_area/BIDW1/backupset/2010_02_11/o1_mf_nnnd1_TAG20100211T022457_5q7d6cgv_.bkp tag=TAG20100211T022457 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15 channel ORA_DISK_1: starting incremental level 1 datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current control file in backup set including current SPFILE in backup set channel ORA_DISK_1: starting piece 1 at 11-FEB-10 channel ORA_DISK_1: finished piece 1 at 11-FEB-10 piece handle=/oracle/flash_recovery_area/BIDW1/backupset/2010_02_11/o1_mf_ncsn1_TAG20100211T022457_5q7d6t16_.bkp tag=TAG20100211T022457 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 11-FEB-10 Starting backup at 11-FEB-10 current log archived using channel ORA_DISK_1 channel ORA_DISK_1: starting archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=22 RECID=51 STAMP=710648715 channel ORA_DISK_1: starting piece 1 at 11-FEB-10 channel ORA_DISK_1: finished piece 1 at 11-FEB-10 piece handle=/oracle/flash_recovery_area/BIDW1/backupset/2010_02_11/o1_mf_annnn_TAG20100211T022515_5q7d6vg7_.bkp tag=TAG20100211T022515 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 11-FEB-10 RMAN>
Now, let’s see if we can restore:
RMAN> startup mount Oracle instance started database mounted Total System Global Area 422670336 bytes Fixed Size 1336960 bytes Variable Size 356518272 bytes Database Buffers 58720256 bytes Redo Buffers 6094848 bytes RMAN> restore database; Starting restore at 11-FEB-10 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=18 device type=DISK channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00001 to /oracle/oradata/bidw1/system01.dbf channel ORA_DISK_1: restoring datafile 00002 to /oracle/oradata/bidw1/sysaux01.dbf channel ORA_DISK_1: restoring datafile 00003 to /oracle/oradata/bidw1/undotbs01.dbf channel ORA_DISK_1: restoring datafile 00004 to /oracle/oradata/bidw1/users01.dbf channel ORA_DISK_1: restoring datafile 00005 to /oracle/oradata/bidw1/example01.dbf channel ORA_DISK_1: restoring datafile 00006 to /oracle/oradata/bidw1/tdrep01.dbf channel ORA_DISK_1: restoring datafile 00007 to /oracle/oradata/bidw1/target01.dbf channel ORA_DISK_1: reading from backup piece /oracle/flash_recovery_area/BIDW1/backupset/2010_02_11/o1_mf_nnnd0_TAG20100211T015557_5q7bhz1o_.bkp channel ORA_DISK_1: piece handle=/oracle/flash_recovery_area/BIDW1/backupset/2010_02_11/o1_mf_nnnd0_TAG20100211T015557_5q7bhz1o_.bkp tag=TAG20100211T015557 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:06:37 Finished restore at 11-FEB-10 RMAN> recover database; Starting recover at 11-FEB-10 using channel ORA_DISK_1 channel ORA_DISK_1: starting incremental datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set destination for restore of datafile 00001: /oracle/oradata/bidw1/system01.dbf destination for restore of datafile 00002: /oracle/oradata/bidw1/sysaux01.dbf destination for restore of datafile 00003: /oracle/oradata/bidw1/undotbs01.dbf destination for restore of datafile 00004: /oracle/oradata/bidw1/users01.dbf destination for restore of datafile 00005: /oracle/oradata/bidw1/example01.dbf destination for restore of datafile 00006: /oracle/oradata/bidw1/tdrep01.dbf destination for restore of datafile 00007: /oracle/oradata/bidw1/target01.dbf channel ORA_DISK_1: reading from backup piece /oracle/flash_recovery_area/BIDW1/backupset/2010_02_11/o1_mf_nnnd1_TAG20100211T022457_5q7d6cgv_.bkp channel ORA_DISK_1: piece handle=/oracle/flash_recovery_area/BIDW1/backupset/2010_02_11/o1_mf_nnnd1_TAG20100211T022457_5q7d6cgv_.bkp tag=TAG20100211T022457 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:15 starting media recovery media recovery complete, elapsed time: 00:00:03 Finished recover at 11-FEB-10 RMAN> alter database open; database opened RMAN>
So that’s it for the Three “R”’s. I had a lot of fun revisiting the “operations” side of the house, and logging in as SYSDBA again. It’s amazing how it all just came back to me… I didn’t have to look at the manuals at all. Okay… maybe once.
Data Warehouse Fault Tolerance Part 2: Restarting
In my last post, I described the First “R” in data warehouse fault tolerance: Resuming. As I mentioned in the introduction to this series, my goal is a triage approach where the simple things, such as space errors, are handled effortlessly without repercussions. But what happens when the errors are not so simple, and Oracle’s built in resuming functionality can’t catch it? In these cases, the ETL processing will actually error or fail, and the cause will have to be corrected before the load can be restarted.
There are numerous approaches to crafting sustainable ETL; as a matter of fact, Peter Scott wrote a post by that very title. Jon Mead contributed this one about resuming ETL processes. Just a note of clarification: what Jon describes as “resuming” is what I am describing here as “code-controlled restarting”: building smart ETL process flows and instrumented mappings so that a record is kept of what’s already been run. This is a required component, and I recommend coding best practices such as these into all ETL processes. But the restartability feature I’m focusing on is “data management restartability”, which deals with controlling data sets after failures. So the feature that I’m plugging in for the Restartability phase is Oracle’s Flashback functionality.
Flashback provides the capability to revert the entire database, or smaller portions of it, to a particular point in time. For Oracle, a “point in time” is always referenced by the System Change Number(SCN), an internal clock for the Oracle Database. It auto-increments every time a transaction commits, but other sources, such as the SMON process, can increment the SCN as well. The current SCN can be viewed in many of the data dictionary tables, as well as using the DBMS_FLASHBACK server package.
SQL> select current_scn,
2 dbms_flashback.get_system_change_Number
3 from v$database;
CURRENT_SCN | GET_SYSTEM_CHANGE_NUMBER
----------- | ------------------------
2536238 | 2536238
1 row selected.
SQL>
We can convert from SCN’s to timestamps and back again, but this conversion is not exact. The Oracle documentation states that the functions are precise to about 3 seconds, which is evident from this example:
SQL> select SCN_TO_TIMESTAMP(2536238) scn
2 from dual;
SCN
------------------------------
02/09/2010 12.47.26000000000
1 row selected.
SQL> select TIMESTAMP_TO_SCN('02/09/2010 12.47.26000000000') ts
2 from dual;
TS
----------
2536237
1 row selected.
SQL>
Even though we access both Flashback Database and Flashback Table with the same general syntax and specify SCN’s for both incarnations, the technical implementation under the hood is drastically different. Flashback Table is completely an UNDO operation, and is really not a new feature at all. Oracle has always used the UNDO space (rollback segments before that) to manage the state of tables as of a particular SCN to allow the robust multi-versioning that keeps reads and writes from blocking one another. Flashback Table is just an “opening” of the multi-version API, in a manner of speaking, so that any SCN can be viewed as long as sufficient UNDO exists.
Flashback Database, on the other hand, doesn’t use UNDO at all, instead using new instance file structures called flashback logs in conjunction with a little bit of archived redo. Flashback logs contain prior versions of changed blocks, and we use the version of the block just prior to the SCN of interest and put them back in the datafiles, followed by redo log recovery to get the database to the exact point of the SCN.
So what part does Oracle’s Flashback technology play with data warehouse fault tolerance, specifically in the area of Restartability? Some aspect of the load will likely need to be “undone” before we can continue, and this is where Flashback fits in neatly, as demonstrated in the following examples.
I created copies of the CUSTOMERS, PRODUCTS and SALES tables from the SH schema and inserted the rows from there as well. Before I start, I need to enable row movement on the new SALES table. This would need to be implemented for all tables in the data warehouse that are a consideration for Flashback Table:
SQL> alter table target.sales enable row movement;
Table altered.
SQL>
SQL> SELECT count(*) FROM target.products;
COUNT(*)
----------
72
1 row selected.
SQL> SELECT count(*) FROM target.customers;
COUNT(*)
----------
55500
1 row selected.
SQL> SELECT count(*) FROM target.sales;
COUNT(*)
----------
918843
1 row selected.
SQL>
Next I’ll get ready to execute my code. First, I’ll create what’s called a “restore point” in the database. This allows me to give an intelligent name to a particular SCN and is similar to tagging a release in Subversion. Before each new step in the process, I’ll create a restore point so that each phase has a tagged, referenceable SCN. As I’m using the concept of a unique, sequence-generated number for each batch that runs (Jon calls it an “execution ID” in his posting above), I’ll work that number into the name of my restore points.
SQL> create restore point dw_load_1001; Restore point created. SQL>
Next… I do the processing that moves the necessary files into place (if any), prepares and loads the ODS tables, etc. After that… I move into the load of the dimensional model itself:
SQL> create restore point load_customers_1001; Restore point created. SQL> exec dw_load.load_customers; Number of records loaded: 0 PL/SQL procedure successfully completed. SQL> create restore point load_products_1001; Restore point created. SQL> exec dw_load.load_products; Number of records loaded: 72 PL/SQL procedure successfully completed. SQL> create restore point load_sales_1001; Restore point created. SQL> exec dw_load.load_sales; 5 indexes and 0 local index partitions affected on table TARGET.SALES Number of records loaded: 699999 Rebuild processes for unusable indexes on 28 partitions of table TARGET.SALES executed No matching unusable global indexes found PL/SQL procedure successfully completed. SQL>
So the data warehouse load ran without error, so I can assume that it was successful, right? In looking back over the log, I see that no rows were actually loaded into the CUSTOMERS table. After researching the issue, I discover that the Change Data Capture process on the source system is experiencing errors, and there were no rows published to the CUSTOMERS change set. Since the load didn’t technically fail, the process continued to the load of the fact table, and it’s very likely that many of the rows in the fact table have the wrong surrogate key from the CUSTOMERS table.
In describing my triage approach from earlier postings, the “aftermath” is exactly what I’m trying to avoid. In my experience, ETL load failures and the subsequent aftermath (investigations, data corrections, and reloads) cause more downtime than any other hardware or software related issues. But with the approach I’ve put into place, this aftermath shouldn’t concern me, because now I can simply “undo” it (pun intended).
SQL> flashback table target.sales to restore point load_sales_1001;
Flashback complete.
SQL> select count(*) from target.sales;
COUNT(*)
----------
918843
1 row selected.
SQL> create restore point new_load_customers_1001;
Restore point created.
SQL> exec dw_load.load_customers;
Number of records loaded: 99
PL/SQL procedure successfully completed.
SQL> create restore point new_load_sales_1001;
Restore point created.
SQL> exec dw_load.load_sales;
5 indexes and 0 local index partitions affected on table TARGET.SALES
Number of records loaded: 699999
Rebuild processes for unusable indexes on 28 partitions of table TARGET.SALES executed
No matching unusable global indexes found
PL/SQL procedure successfully completed.
SQL>
Instead of flashing back, I could try to sort out the issue. For instance, if I’m attaching the unique execution ID to every row in the fact table, either directly, or through an AUDIT dimension table, then I could probably identify the rows for this run. But why would I do this when the Flashback functionality is already available to me?
My test case above was a simple one; I was able to proceed just by flashing back a single table before restarting the process. However, in a large enterprise data warehouse, the effort involved in a typical aftermath could be staggering depending on how many fact tables are involved, how many dimension tables track history with SCD Type 2 changes, etc. Combine that with the possible need to flashback ODS tables, history tables, persistent staging tables, etc. I’ve seen numerous situations where the exact ramifications are tough to quantify: we know what broke, but we have no idea what needs to be fixed. Perhaps there was a hardware failure in the middle of an ETL load, and it’s hard to identify just exactly which tables were loaded and which ones weren’t. In this case, what I really need is the ability to do a complete “do-over”: put everything back the way it was prior to the beginning of the load, and just restart everything.
Enter Flashback Database. So I’ll demonstrate what’s required to enable this feature, and then I’ll replay the test case above and solve it from this angle.
I first need to put my database in Archive Log Mode, as archived redo is a required component of the feature:
SQL> startup mount ORACLE instance started. Total System Global Area 422670336 bytes Fixed Size 1336960 bytes Variable Size 335546752 bytes Database Buffers 79691776 bytes Redo Buffers 6094848 bytes Database mounted. SQL> alter database archivelog; Database altered. SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 25 Next log sequence to archive 27 Current log sequence 27 SQL>
Next, I need to configure the Flash Recovery Area, which is a file system on the server where the database will create the flashback logs:
SQL> alter system set db_recovery_file_dest_size=3G; System altered. SQL> Alter system set db_recovery_file_dest='/oracle/flash_recovery_area'; System altered. SQL>
Finally, I need to set the flashback_retention_target parameter, which instructs the Flash Recovery Area on our needs for retention. This parameter is actually in minutes… thanks for the consistency Oracle. After that, I just enable flashback and open the database:
SQL> alter system set db_flashback_retention_target=2880; System altered. SQL> alter database flashback on; Database altered. SQL> alter database open; Database altered. SQL>
So, Flashback Database should be ready to use. I’ll take a quick look and see if the database thinks it’s ready:
SQL> select oldest_flashback_scn,
2 oldest_flashback_time,
3 startup_time
4 from v$flashback_database_log
5 cross join v$instance;
OLDEST_FLASHBACK_SCN | OLDEST_FLASHBACK_TIME | STARTUP_TIME
-------------------- | ---------------------- | ----------------------
2912097 | 02/10/2010 12:10:30 AM | 02/10/2010 12:09:08 AM
1 row selected.
Elapsed: 00:00:00.15
SQL>
Now I’ll flashback the entire database to the very first restore point I created: dw_load_1001:
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1336960 bytes
Variable Size 343935360 bytes
Database Buffers 71303168 bytes
Redo Buffers 6094848 bytes
Database mounted.
SQL> flashback database to restore point dw_load_1001;
Flashback complete.
SQL> alter database open resetlogs;
Database altered.
SQL> SELECT count(*) FROM target.products;
COUNT(*)
----------
72
1 row selected.
SQL> SELECT count(*) FROM target.customers;
COUNT(*)
----------
55500
1 row selected.
SQL> SELECT count(*) FROM target.sales;
COUNT(*)
----------
918843
1 row selected.
SQL>
So the immediate downside of this approach is that it requires the involvement of the operations team because the database has to be in mount mode, and the data warehouse is not available during this slight outage. However, when compared with the time it might take to sort out and correct massive aftermath scenarios, this seems to be the preferable choice. Is the data warehouse really “available” if data corrections and data reloads are occurring? I would rather involve the operations team for a quick, concrete fix so the reload can complete as soon as possible.
The next “R” is Restoring, though it really involves putting the pieces in place for a scalable Backup strategy. And “Backup” doesn’t start with an “R”.