In this blog post, we shall see the various types of modeling techniques that we can use on a parentchild hierarchy. The reverse star schema is built logically in the rpd bmm layer. Dimensional hierarchies are built by obiee repository developers in conjunction with the owners of the data, to allow users to drill down into dimensional data. Obiee 11g addresses these by where possible moving administration and configuration into fusion middleware control also referred to as enterprise manager. It uses the hierarchical level to pin the the aggregate. Nov 23, 2010 as i had explained in my previous post here, parent child hierarchies introduced in bi ee 11g are much more flexible in terms of handling aggregation of measures when compared with a level based hierarchy. A level based hierarchy is created for products as follows.
Oracle bi ee 11g parent child hierarchies multiple. The separator is, which can be modified based on your requirement. To specify nested aggregation, you must define a column with a default aggregation rule and then request the aggregation of the column in a sql. Creating and managing dimensions with levelbased hierarchies. It should be similar to the methods used in obiee prompts last year, last month and custom dates mentioned above. An example of nested aggregation in obiee 11g aggregate of.
Obiee purpose of aggregate tables in dimension modelling. Server will use it to identify aggregate tables and mini. One of the new feature of 11g is the aggregate at function. Level based measures in oracle business intelligence.
Security in obiee is defined in terms of roles that are aligned to different directory server groups and users. I want the cumulative column to be a running aggregate for that particular service type. Based on dimension aggregation method obiee 11g user10275812 jul 6, 2011 11. Because the dsn created upon install for each oracle bi. This obiee 11g video training tutorial demonstrates how to create dimension hierarchies in the business model and mapping layer bmm. This chapter explains how to set up and use aggregate persistence in oracle. Based on dimension aggregation method obiee 11g user10275812 jul 2, 2011 7. Obiee 11g beginners guide business intelligence solution providers 7 8. In the browse dialog box, click new, select a dimension over which you want to aggregate, and then click ok. In the business model and mapping layer, a dimension object represents a hierarchical organization of logical columns attributes. Similarly, you can apply level based measures on the time hierarchies. It does this by detecting nulls in either leaf levels for ragged hierarchies or other levels for skiplevel hierarchies and use this to modify how the new hierarchical column type in answers handles the missing levels.
Obiee primarily accesses planning using standard essbase integration techniques. Creating aggregates using aggregate persistence wizard in. Yesterday, i stumbled upon an issue where i was requested to show subtotal for some metrics in the table where as not to show subtotal for other metrics. In the logical dimension dialog, in the general tab. To start off with something familiar, the administration tool that was present in obiee 10g is also present in 11g, is also windows based, and is used to maintain the semantic model used by. For example, if we are recording sales by month, then sumsales will allow us to report on those amounts at a higher level such as year. Jul 12, 2010 obiee 11g can handle this though with the new ragged and skiplevel support for level based hierarchies. This functionality is really tied to the last and first function in order to define its grain. Posted by darlawalker programmer analyst on feb 19 at 6. Oct 27, 2014 here is an example of a level based measure using a products and revenue table. This option is only available when there is at least one dimension table that has no dimension associated with it. Dimension table stores the attributes that describe objects in a fact table. Obiee 10g11g performance tuning options infosemantics. Obiee tutorial a beginner guide by obiee professionals.
In the logical column dialog box, click the aggregation tab. Obiee integrates with planning directly through relational database access. Obiee 10g11g repository rpd file gerardnico the data. Jul 04, 2014 this obiee 11g video training tutorial demonstrates how to create dimension hierarchies in the business model and mapping layer bmm. In addition to familiar sql aggregation functions such as sum, max, min, average, and count, obiee also supports an aggregation function called aggregate. Jan 16, 2015 an example of nested aggregation in obiee 11g aggregate of an aggregate based on condition 16 jan before i begin with an example to illustrate how and why we want to implement count distinct of a measure based on a condition i would like to highlight the following posts that i found while researching on this topic. By default, when we drill down within the products hierarchy from brand level to product level, the corresponding revenue drills down to product level. This tutorial covers the creation of oracle business intelligence enterprise edition obiee metadata for access to oracle database 11g olap option data and calculations by using the oracle olap 11g analytic workspace manager plugin for obiee. Analytic workspace manager initially chooses cost based aggregation with 20% precomputed values for the bottom partitions and 0% for the top partition. In the aggregation tab, select the based on dimensions check box. This is a level based hierarchy and will be used to show. In the business model and mapping layer of the administration tool, rightclick a business model and select new object logical dimension dimension with level based hierarchy. Obiee 11g dimension hierarchies obiee online training.
The diagram above shows obiee application layer architecture. It was recommended in 10g, but it is mandatory in 11g. Depending on the measure and the dimension column endusers choose in the query, bi server will pick either an aggregate table or a detail. Obiee change the default aggregation set in the rpd in answers. This setting means that 20% of the aggregate values will be calculated and stored during data maintenance, and 80% will be calculated in response to a. I also noticed that if i do not include any attribute from po dimension in my report, obiee generates a physical query joining fact with all 10 dimensions, brings in all the data into presentation layer and then does the.
In the browse dialog, select a dimension over which you want to aggregate, and then click ok. Aggregation based on dimensions in obiee beyond blog. Therefore in this case the aggregation should be last. Obiee reduces cost with a proven webbased serviceoriented architecture that integrates with. An example of nested aggregation in obiee 11g aggregate. Oct 10, 2014 posts about aggregation in obiee 11g written by bicoach. Nov 21, 2010 oracle business intelligence enterprise edition, obiee, oracle bi, obi ee, bi, best practices, examples and technical solutions from data modelling to reports and dashboard. To learn how to set up usage tracking, you can go through the tutorial on setting up usage tracking in oracle bi 11g. This is a levelbased hierarchy and will be used to show.
At this point we can clearly see that we need to apply two different types of aggregations for the stock measure. String aggregation in obiee its all about data by jethin. Business models are always dimensional, unlike objects in the physical layer, which reflect. Importing obiee metadata into an obiee 11g repository task 3. I have a factdim combination in obiee that looks something like this. Business intelligence bi solutions and services, big data, training, barcelona, dubai, abu dhabi. Setting up dimensionspecific aggregate rules for logical. There is no specific explicit complex join like in obiee 11g. You should also determine the database where the aggregates are to be deployed in.
One or more logical dimension tables can be associated with at most one dimension object. Obiee security is defined by use of a role based access control model. I was playing around with answers and noticed that when i click on the formula button and changed the aggregation from default to average, nothing changed. Set aggregation content for logical table sources in obiee. The business model in obiee allows for measures to be aggregated using a variety of different functions. Based on dimension aggregation method obiee 11g oracle. Introduction to oracle business intelligence enterprise. May 24, 20 usage tracking is also helpful in determining which user queries are creating performance bottlenecks, based on query frequency and response time. The business model and mapping layer of the oracle bi repository defines the business, or logical, model of the data and specifies the mapping between the business model and the physical layer schemas. The aggregate functions cannot be used to form nested aggregation in expressions on logical columns that have a default aggregation rule defined in the aggregation tab of the logical column dialog. Exporting obiee metadata from analytic workspace manager task 2. Export bi catalog captions from command line in obiee 11g.
Generally, an ebook can be downloaded in five minutes or less. The preceding example connects to an rpd using the dsnname connection. The oracle application layer or popularly known as the fusion middleware layer is built upon a common domain based architecture that can support as a set of common core functionality like security, scalability etc across various oracle products. Click ok to close the logical table source dialog box. Obiee running sum rsum obiee repository aggregation rules based on dimension dimension specific aggregate obiee ora00937 not a singlegroup group function. Problem is you cant select a hierarchy level in the formula editor, so you have to some old school typing. Creating obiee metadata for olap 11g cubes purpose.
Obiee offer the possibility to have differents measure aggregation rule based on the dimension. The sampleapps d1 products level based hierarchy is based on two logical table sources. We save tables with aggregated data like yearly 1 row, quarterly 4 rows, monthly 12. Delete the aggregations set for that dimension in the content tab of the fact. Obiee provides complete access to all planning data through the analytic data model adm layer.
The directory server user and group managed by the authentication provider. This function is available for use in formulas written in answers. When a measure is based on dimensions, and data is dense, the oracle bi server optimizes the sql statements sent to the database to improve performance. Describing obiee plug in for analytic workspace manager overview of creating obiee metadata from a cube task 1. In the aggregation tab, select based on dimensions. To gain in depth knowledge and be on par with practical experience, then explore obiee 11g training online. If you know all the steps to configure obiee, you can find the archive which contains the metadata based on the sh schema here. The datamart or subject area that you will query with obiee contains two types of tables.
These type of measures are called level based measures. Obiee 10g 11g measures columnsattributes obiee evaluate embedded db functions. The first function is limited to defining dimension specific aggregation rules in a repository. Depending on your configuration, you may enable some parameters in. Confirm that aggregation content, group by is set to logical level and the logical level is set to time detail for the h1 time logical dimension. Obiee aggregation rules in logical sql with the group by and by clause. An example of nested aggregation in obiee 11g aggregate of an aggregate based on condition 16 jan before i begin with an example to illustrate how and why we want to implement count distinct of a measure based on a condition i would like to highlight the following posts that i found while researching on this topic. Obis nested aggregation ralph kimball great bi with. Obiee repository aggregation rules based on dimension. Mar 21, 20 when i create a report in obiee with po amount and an attribute from po dimension, it does only sum aggregation on po amount. Count distinct bi solutions by shankha bhattacharya.
Those prompts should be dynamic in setting the filter for the relevant aggregation level. I have 2 fact tables 1 base fact f1 having base amount, amountytd 2 aggregated fact f2 having aggregated values of amount,amountytd. Create a logical table source in the dimension at each level that matches. Hi all, i am trying to setup aggregate navigation and dimension based aggregation in obiee 11g. Once the dimension hierarchies are created, level based measures can be created by double clicking on the total revenue column in the logical table and setting the level in the levels tab. Obiee binning aggregation without including lowest dimension. The oracle by example series for the version 10g of obiee is also based on the sh sample schema. To achieve this we are going to set an aggregation based on dimensions to our measures in the obi repository. For the h2 product logical dimension, select product total from the logical level dropdown list to specify that this measure should be calculated at the grand total level in the product hierarchy.