OBIEE11g Latest Version as of 20-04-2014: OBIEE Version 11.1.1.7.140415 Now Released

Hello Everyone,

The Latest version of OBIEE as of 15-APR-2014 has been released few days ago. Please check the following link for more details.

https://blogs.oracle.com/proactivesupportEPM/entry/obiee_11_1_1_75

Thanks,
Nagarjuna

OBIEE11g: Managing the Oracle BI Presentation Services Cache Settings

When users run analyses, Presentation Services can cache the results of those analyses. Presentation Services determines if subsequent analyses can use cached results. If the cache can be shared, then subsequent analyses are not stored.
The files for the Presentation Services cache have names such as nQS_xxxx_x_xxxxxx.TMP. The files are created by the ODBC driver but generally do correspond to ODBC requests that the Presentation Services cache keeps open. The files are stored in the following directory:
ORACLE_INSTANCE\tmp\OracleBIPresentationServices\coreapplication_obipsn\obis_temp
The files for the cache are removed whenever Presentation Services shuts down cleanly. If Presentation Services shuts down unexpectedly, then various cache files might be left on disk. You can delete the files when Presentation Services is not running.
The Presentation Services cache is different from the cache that is accessed by the Oracle BI Server. You can change the defaults for the Presentation Services cache by modifying the instanceconfig.xml file to include the cache entries.
The following procedure provides information about configuration changes with which you can manage the Presentation Services cache.


To manually edit the settings for managing the cache:

1. Open the instanceconfig.xml file for editing
   
2. Locate the section in which you must add the elements
   
   Note:

   Avoid specifying values of less than 3 minutes for the elements that affect minutes. At such a low amount of time, refreshes can occur frequently, which can negatively affect performance and cause flickering on the screen.
   
3. Include the elements and their ancestor elements as appropriate, as shown in the following example:
   

   <ServerInstance>
     <Cache>
       <Query>
         <MaxEntries>100</MaxEntries>
         <MaxExpireMinutes>60</MaxExpireMinutes>
         <MinExpireMinutes>10</MinExpireMinutes>
         <MinUserExpireMinutes>10</MinUserExpireMinutes>
       </Query>
     </Cache>
   <ServerInstance>
   

4. Save your changes and close the file.
   
5. Restart Oracle Business Intelligence.
   

Table 7-7 Elements for Configuring the Cache for Presentation Services

Element	Description	Default Value
MaxEntries	Specifies the maximum number of open record sets that Presentation Services keeps open at any one time. The minimum value is 3. For systems under significant loads, you can increase this value to 700 or 1000.	500
MaxExpireMinutes	Specifies the maximum amount of time, in minutes, that an entry in the cache can exist before it is removed. Depending on the number of analyses being run, an entry might be removed before the time limit expires.	60 (one hour)
MinExpireMinutes	Specifies the minimum amount of time, in minutes, that an entry in the cache can exist before it is removed. The setting for CacheMinUserExpireMinutes can force an entry for a particular user to exist for a longer time than that specified by the CacheMaxExpireMinutes element.	10
MinUserExpireMinutes	Specifies the minimum amount of time, in minutes, that an entry in the cache can exist after it has been viewed by a user. For example, if CacheMaxExpireMinutes is set to 60 minutes and a user views the entry during the 59th minute, the entry exists for that user for an additional 10 minutes. The user can continue paging through the data without requiring a new analysis to be run.	10

OBIEE11g: Changing the Configuration Limits of PROMPTS in INSTANCECONFIG.XML file of OBIEE11g and Auto Complete Prompt Values

You can configure settings that affect the way that users work with prompts, as described in this section.
To configure for prompts:

1. Open the instanceconfig.xml file
   
2. Locate the sections in which you must add the elements
   
3. Include the elements and their ancestor elements as appropriate, as shown in the following example:
   

   <ServerInstance>
     <Prompts>
       <MaxDropDownValues>256</MaxDropDownValues>
       <AutoApplyDashboardPromptValues>true</AutoApplyDashboardPromptValues>
       <AutoSearchPromptDialogBox>true</AutoSearchPromptDialogBox>
       <AutoCompletePromptDropDowns>
         <SupportAutoComplete>true</SupportAutoComplete>
         <CaseInsensitive>true</CaseInsensitive>
         <MatchingLevel>MatchAll</MatchingLevel>
         <ResultsLimit>50</ResultsLimit>
       </AutoCompletePromptDropDowns>
     </Prompts>
   </ServerInstance>
   

     
   Note: This example does not include elements that might exist in the file, but that are centrally managed by Fusion Middleware Control and cannot be changed manually.
   
4. Save your changes and close the file.
   
5. Restart Oracle Business Intelligence.
   

Table 18-4 Elements for Configuring Prompts

Element	Description	Default Value
AutoApplyDashboardPromptValues	Specifies whether to display various fields, as described in the following list: If True, then The Show Apply Button and Show Reset Button fields are displayed on the Edit Page Settings dialog. The Prompts Apply Buttons and Prompts Reset Buttons fields are displayed in the Dashboard Properties dialog. The Prompt Buttons on Current Page option is displayed on the Dashboard builder's Tools menu. If False, then The Show Apply button and Show Reset button fields are not displayed on the Edit Page Settings dialog. The Prompts Apply Buttons and Prompts Reset Buttons fields are not displayed in the Dashboard Properties dialog. The Prompt Buttons on Current Page option is not displayed on the Dashboard builder's Tools option.	True
AutoSearchPromptDialog	Specifies whether search results are displayed and highlighted when the user types the search parameter (without clicking the Search button).	True
CaseInsensitive	Specifies whether the auto-complete functionality is case-insensitive. If set to True, case is not considered when a user enters a prompt value such as "Oracle" or "oracle." If set to False, case is considered when a user enters a prompt value, so the user must enter "Oracle" and not "oracle" to find the Oracle record. The system recommends the value with the proper case.	True
Matching Level	Specifies whether the auto-complete functionality uses matching to find the prompt value that the user enters into the prompt field. These settings do not apply when the user accesses the Search dialog to locate and specify a prompt value. Use the following settings: StartsWith — Searches for a match that begins with the text that the user types. For example, the user types "M" and the following stored values are displayed: "MicroPod" and "MP3 Speakers System". WordStartsWith — Searches for a match at the beginning of a word or group of words. For example, the user types "C" and the following values are displayed: "ComCell", "MPEG Camcorder", and "7 Megapixel Digital Camera". MatchAll — Searches for any match within the word or words.	MatchAll
MaxDropDownValues	Specifies the maximum number of choices to display in the following locations: In choice lists in dashboard prompts. In the Available list of the Select Values dialog that is displayed when the user clicks the Search link in a prompt and the More link to display additional choices. In the Available list of the Select Values dialog when the user performs a search in that dialog.	256
ResultsLimit	Specifies the number of matching values that are returned when the auto-complete functionality is enabled.	50
SupportAutoComplete	Enables or disables the auto-complete functionality of prompts. A setting of True turns auto-complete on, which means that the Prompts Auto-Complete field is displayed and is set to On in the My Account dialog and in the Dashboard Properties dialog. A setting of False turns auto-complete off, which means that the auto-complete fields in the My Account and Dashboard Properties dialogs are not available.	False, unless you are running Oracle BI EE on the Oracle Exalytics In-Memory Machine

OBIEE11g: Changing the Configuration Limits of Different Views(Table,Pivot Table,Trellis,Charts etc) in INSTANCECONFIG.XML file of OBIEE11g

Scenario:

The number of records that should be shown in the Views(Table,Pivot Table,Trellis,Charts etc) will be configured in the INSTANCECONFIG.XML file. There are some default settings which is applied to the Views, You can change those default settings if you want to increase/decrease the limits by editing or adding few tags in INSTANCECONFIG.XML file. For example the default number of records displayed in any of the views would be 65,000 if the reports is getting more than the default limit then you get an error message saying that max number of cube records are exceeded. In this kind of situations we can fix this issue by changing the default configurations.

<ODBC>
<!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control-->
<ResultRowLimit>700000</ResultRowLimit>
</ODBC>

NOTE: 
1) You should take a back up of Instanceconfig.xml file before editing.
2) These tags should be added in between <ServerInstance> </ServerInstance> only
3) Few of the tags will be present in the instanceconfig.xml file by default in that case we dont need to add them again just go and change the number in between the tags as per requirement. If you dont find the tag related to a specific view then you can add it as shown below.

 To manually edit the settings:

1. Open the instanceconfig.xml file for editing (
   ORACLE_INSTANCE\config\OracleBIPresentationServicesComponent\coreapplication_obipsn
   For example:
   \instances\instance1\config\OracleBIPresentationServicesComponent\coreapplication_obips1)
   
   
2. Locate the Cube section, in which you must add the following elements: If you dont find the cube section then add it.
   
   • CubeMaxRecords — Specifies the maximum number of records that are returned by an analysis for the view to process. This roughly governs the maximum number of cells that can be populated in a view; unpopulated cells in a sparse view do not count. The default is 40000.
     
   • CubeMaxPopulatedCells — Specifies the maximum number of cells in a view that can be populated with data from the Oracle BI Server. The default is 120000.
     
3. Include the elements and their ancestor elements as appropriate, as shown in the following example:
   

    <ServerInstance>

   <ODBC>
   <!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control-->
   <ResultRowLimit>700000</ResultRowLimit>
   </ODBC> 

     <Views>
       <Cube>
         <CubeMaxRecords>40000</CubeMaxRecords>
         <CubeMaxPopulatedCells>120000</CubeMaxPopulatedCells>
       </Cube>
         <Table>
           <MaxCells>10000</MaxCells>
           <MaxPagesToRollOutInDelivery>1000</MaxPagesToRollOutInDelivery>
           <MaxVisiblePages>1000</MaxVisiblePages>
           <MaxVisibleRows>500</MaxVisibleRows>
           <MaxVisibleSections>25</MaxVisibleSections>
           <DefaultRowsDisplayed>30</DefaultRowsDisplayed>
           <DefaultRowsDisplayedInDelivery>250</DefaultRowsDisplayedInDelivery>
           <DefaultRowsDisplayedInDownload>65000</DefaultRowsDisplayedInDownload>
         </Table>
         <Pivot>
           <MaxPagesToRollOutInDelivery>1000</MaxPagesToRollOutInDelivery>
           <MaxVisibleColumns>300</MaxVisibleColumns>
           <MaxVisiblePages>1000</MaxVisiblePages>
           <MaxVisibleRows>500</MaxVisibleRows>
           <MaxVisibleSections>25</MaxVisibleSections>
           <DefaultRowsDisplayed>30</DefaultRowsDisplayed>
           <DefaultRowsDisplayedInDelivery>250</DefaultRowsDisplayedInDelivery>
           <DefaultRowsDisplayedInDownload>65000</DefaultRowsDisplayedInDownload>
         </Pivot>
         <Trellis>
           <Simple>
               <MaxCells>1000</MaxCells>
               <MaxVisibleSections>10</MaxVisibleSections>
               <MaxVisiblePages>1000</MaxVisiblePages>
               <MaxVisibleRows>100</MaxVisibleRows>
               <MaxVisibleColumns>75</MaxVisibleColumns>
               <MaxPagesToRollOutInDelivery>1000</MaxPagesToRollOutInDelivery>
               <DefaultRowsDisplayed>10</DefaultRowsDisplayed>
               <DefaultRowsDisplayedInDelivery>100</DefaultRowsDisplayedInDelivery>
               <DefaultRowsDisplayedInDownload>6500</DefaultRowsDisplayedInDownload>
           </Simple>
           <Advanced>
               <MaxCells>5000</MaxCells>
               <MaxVisibleSections>50</MaxVisibleSections>
               <MaxVisiblePages>1000</MaxVisiblePages>
               <MaxVisibleRows>250</MaxVisibleRows>
               <MaxVisibleColumns>150</MaxVisibleColumns>
               <MaxPagesToRollOutInDelivery>1000</MaxPagesToRollOutInDelivery>
               <DefaultRowsDisplayed>25</DefaultRowsDisplayed>
               <DefaultRowsDisplayedInDelivery>250</DefaultRowsDisplayedInDelivery>
               <DefaultRowsDisplayedInDownload>10000</DefaultRowsDisplayedInDownload>
           </Advanced>
         </Trellis>
         <Charts>
           <MaxVisibleColumns>2000</MaxVisibleColumns>
           <MaxVisiblePages>1000</MaxVisiblePages>
           <MaxVisibleRows>2000</MaxVisibleRows>
           <MaxVisibleSections>25</MaxVisibleSections> 
           <EmbedFonts>True</EmbedFonts>
           <SectionSliderDefault>150</SectionSliderDefault>
           <SectionSliderLimit>300</SectionSliderLimit>
           <JavaHostReadLimitInKB>4096</JavaHostReadLimitInKB>
         </Charts>
         <Narrative>
           <MaxRecords>40000</MaxRecords>
           <DefaultRowsDisplayed>30</DefaultRowsDisplayed>
         </Narrative>
         <Ticker>
           <MaxRecords>40000</MaxRecords>
         </Ticker>
     </Views>

    </ServerInstance>
   

4. Save your changes and close the file.
   
5. Restart Oracle Business Intelligence Services (OPMN Services).

Element	Description	Default Value	Applicable Views
DefaultRowsDisplayed	Specifies the default number of rows to display in views in analyses and dashboards. This number should not exceed the number that is specified for the MaxVisibleRows element.	25 (10 for Simple Trellis)	Narrative, Pivot Table, Table, Trellis
DefaultRowsDisplayedInDelivery	Specifies the default number of rows that can be included in the view when it is displayed on a dashboard.	100 for Simple Trellis; 250 for Advanced Trellis, Table, and Pivot Table	Pivot Table, Table, Trellis
DefaultRowsDisplayedInDownload	Specifies the default number of rows that can be included in the view when it is downloaded, such as to a PDF file.	65000 (6500 for Simple Trellis; 10000 for Advanced Trellis)	Pivot Table, Table, Trellis
MaxCells	Specifies the maximum number of cells to be displayed in a view. This number should not exceed the product of MaxVisibleColumns times MaxVisibleRows, which is what the system attempts to render.	50000 (1000 for Simple Trellis)	Pivot Table, Table, Trellis
MaxPagesToRollOutInDelivery	Specifies the maximum number of pages that can be included in the view when it is displayed on a dashboard.	1000	Pivot Table, Table, Trellis
MaxRecords	Specifies the maximum number of records that can be included in the view.	40000	Narrative, Ticker
MaxVisibleColumns	Specifies the maximum number of columns to be displayed in a view.	300 (75 or Simple Trellis; 150 for Advanced Trellis)	Graph, Pivot Table, Trellis
MaxVisibleRows	Specifies the maximum number of rows to be displayed in a view. The value of DefaultRowsDisplayed should not exceed this value. For tables and pivot tables, specifies the following: The number of rows that is displayed on the tooltip for the Display Maximum Rows per Page paging control button. The uppermost value to specify for the Maximum Number of Rows to Download and the Maximum Number of Rows Per Page to Include options in Fusion Middleware Control.	500 (100 or Simple Trellis; 250 for Advanced Trellis)	Graph, Pivot Table, Table, Trellis
MaxVisiblePages	Specifies the maximum number of view prompts (or pages in PDF) to be displayed in a view.	1000	Graph, Pivot Table, Table, Trellis
MaxVisibleSections	Specifies the maximum number of sections to be displayed in a view. This element does not apply when a slider is in place for a graph. The SectionSliderDefault and SectionSliderLimit elements apply to limit section values when a slider is in place.	25 (10 or Simple Trellis; 50 for Advanced Trellis)	Graph, Pivot Table, Table, Trellis
JavaHostReadLimitInKB	Specifies the maximum amount of data that is sent to the browser for a single graph.	4096	Graph

OBIEE 11g: Cannot Copy Data from Reports and Paste to the Windows Clipboard

Scenario:  After placing the reports on the dashboard or in the Results tab the user may want to select a specific cell or complete table/pivot table and copy it to the clip board subsequently they would like to copy the clipboard content to excel or csv or text file etc...

We raised a service request to Oracle Support and got the following answer.

OBIEE 11g: Cannot Copy Data from Reports and Paste to the Windows Clipboard (Doc ID 1287265.1)

---

Applies to:

Business Intelligence Server Enterprise Edition - Version 11.1.1.3.0 [1905] and later
Business Intelligence Suite Enterprise Edition - Version 11.1.1.3.0 [1905] and later
Information in this document applies to any platform.

Symptoms

You are not able to copy text from tables or pivot views and paste to the Windows clipboard in OBIEE 11g. It was possible in OBIEE 10g by clicking and marking the area of the content of a table and copying it into the Windows clipboard.

Cause

It is a feature not yet available in OBIEE 11g.
There are two enhancement requests for this functionality:

Bug: 9927279 - 11g beta : unable to copy data from 11g table or pivot table like in 10g
Bug 10406798 - unable to copy data from 11g table or pivot table like in 10g

Solution

Please be advised that the following bugs are still open; however, see if the following work-around steps help:

1. Place the mouse cursor outside the view and click.
2. Drag the mouse across the view to highlight the data you want to copy.
3. Use control+C or copy from the edit menu or right mouse menu to copy the text to the clipboard.

Please note this workaround only works with Internet Explorer.  It does not work with Firefox or Chrome.

References

BUG:9927279 - 11G BETA : UNABLE TO COPY DATA FROM 11G TABLE OR PIVOT TABLE LIKE IN 10G

OBIEE11g: Changing Default Views in the Compound Layout of OBIEE11g

Scenario:

We all know that the default views in the Compound Layout of OBIEE (10g & 11g) are Title and Table. But i would like to have Title and Chart as default views in the compound layout. The following can explain how to get this requirement.


Step 1:

Create a custom xml message file called compoundlayout.xml and save it under the custom messages folder.

Note: If you don’t find the CustomMessages folder in {MW_Home}

\Oracle_BI1\bifoundation\web\msgdb path then create a folder with name “CustomMessages”. 




Step2:

Then create one xml file with the below code and save it in the same path.

Folder location-

{MW_Home}\Oracle_BI1\bifoundation\web\msgdb\customMessages

If you have not done any customization you have to create this folder under msgdb.

<?xml version="1.0" encoding="utf-8"?>

<WebMessageTables xmlns:sawm="com.siebel.analytics.web.messageSystem">

 <WebMessageTable system="Answers" table="ViewDefaults">

<WebMessage name="kuiCriteriaDefaultViewElements" translate="no"><HTML>

 <view signature="compoundView" ><cv signature="titleView" />   

<cv signature="dvtchart" />

  </view>

</HTML>

</WebMessage>

 </WebMessageTable>

</WebMessageTables>

Note that the views will be created in the same order as you define in your xml file.



As per our XML file first Title View will be created and then Chart view.
In the same way you can add multiple views like Narrative,View Selector,Table etc...

You can use the following html tags for those views

<cv signature="narrativeView" />

 <cv signature="viewSelector" />

 <cv signature="tableView" />



Step3:

Restart BI Services and start building reports with new default layout. 

OBIEE11g: Purging Cache Using the Agents in Obiee11g

Purge Cache Using Agents in Obiee11g:

Create a new database by doing a right click on the empty space in the physical layer as shown in the below screenshot

Now you can see a window with the database properties as shown in the below screenshot

 Give a name to the database and select ODBC Basic from the Database drop down.Check in the option Allow direct database requests by default. Please follow the below screenshot.

 Click on Connection Pools tab and then click on Add(+) to create a connection pool for the database

Give the name for the connection pool(In my case i have given the name as PurgeCache) and set the values as per your requirement.

Now click on the Permissions tab then you can see a window as follows

 

Give the access for the users and application roles as per the requirement as shown in the below screenshot 

Click on OK then click on Check Out

Give the User Name and password in the connection pool, Once you enter the password then it will ask you to retype the password. Please follow the below screenshots.

Now you can see the connection pool is successfully created

Check the RPD consistency and then save the repository

Deploy the new  repository in the BI Server (Enterprise Manager) and then restart the services.

Now login to the presentation services and create a new report by using Create Direct Database Request as shown in the below screenshot.

Give the Connection Pool Name and type the following query in the query box

Call SAPurgeAllCache();

  Now Click on Results tab  and then save the report

 

 

We need to create an agent to scheduled this report now. So go and create one new agent to schedule this report.

 

OBIEE 11g: Error: '[Nqserror: 43126] Authentication Failed: Invalid User/Password' when Logging into Answers

To Bottom

---

Applies to:

Business Intelligence Server Enterprise Edition - Version 11.1.1.3.0 [1905] and later
Business Intelligence Suite Enterprise Edition - Version 11.1.1.3.0 [1905] and later
Information in this document applies to any platform.

Symptoms

The BISystemUser in OBIEE 11g (the Administrator User set up during the installation) is unable to log into Answers. But you can log in to Enterprise Manager (EM) and WebLogic Server (WLS) Administration Console without problem as this user.
The BI Server generates the following error:

[nQSError: 43126] Authentication failed: invalid user/password

The Managed Server generates the following error:

<Nov 11, 2010 12:54:49 AM PHT> <Notice> <Security> <server1.companyname.com> <bi_server1> <[ACTIVE] ExecuteThread: '0' for queue: 'weblogic.kernel.Default (self-tuning)'> <> <> <> <1289408089797> <BEA-090078>

ver1> <[ACTIVE] ExecuteThread: '0' for queue: 'weblogic.kernel.Default (self-tuning)'> <> <> <> <1289408089797> <BEA-090078> <User bisystemuser in security realm myrealm has had 5 invalid login attempts, locking account for 30 minutes.>

Cause

The BISystemUser account has become locked.

Solution

You have to create a new user with the same privileges and permissions:

1. Log in to the WebLogic Server Administration Console.
2. Create a new user and assign it to the Administrators Group.
3. Log in to Enterprise Manager to Add this new user to 'BISystem' Role.
4. Navigate to 'Weblogic Domain > bifoundation_domain'
5. Right click on bifoundation_domain.
6. From the resulting menu, click on Security/Credentials menu item
7. In the Credentials Screen expand oracle.bi.system.
8. Choose 'system.user' and click 'Edit'
9. In the resulting pop-up window, enter the new user name and password
10. Re-start the OBIEE components, Managed Server and Admin Server.
11. After ten minutes log in to Answers as the new user.

Dimension Hierarchies in Datawarehousing

Role of Dimensional Hierarchies in Data Analysis (OLAP):

The Hierarchy is a Level based structure where we can drill down the data from Highest level to Lowest Level also we can roll up the data.

Hierarchies plays a vital role in data analysis process (OLAP) in datawarehousing.Hierarchies allows the business users to analyze the data with respect to multiple dimensions. In data warehousing and on-line analytical processing they provide for examining data at different levels of detail. Several types of hierarchies have been presented with issues concerning dependencies and summarizability of data along the levels. Design mechanism for implementation of dimensional hierarchies in datawarehouse logical scheme has been proposed. A hierarchy is a set of levels having many-to-one relationships between each other, and the set of levels collectively makes up a dimension or a tree. A tree shows a hierarchical relationship.

In a relational database, the different levels of a hierarchy can be stored in a single table (as in a star schema) or in separate tables (as in a snowflake schema).

Eg:

• In Product Dimension, a Product Name --> to Product Sub Category. Product Sub Category --> to categories, and categories --> to Segments.
• in the time dimension : Fiscal Year > Fiscal Quarter > Fiscal Month > Fiscal Week > Day
• in the region dimension : cities --> State -->countries -->Regions

To further simplify lets assume the user would like to see the Customer vise revenue, Product vice revenue and Time vise revenue and so on. We can create multiple reports with customer, Product and Time etc. Now the user would like to pick a specific dimension and do a complete analysis at different levels. If we consider Time, The user can analyze the revenues at Year level, Half Yearly level, Quarterly level, Month level, week level and Day level. Again the user may want to select a particular year and do his analysis. To get this requirement we may need to create multiple reports and even after creating multiple reports it may not serve the exact purpose. This is where we look for the best option to makes the things easy. Dimension Hierarchy is the exact solution for this scenario.

By using a Hierarchy a business user can analyze the data with different dimensions; different levels and He can choose a particular element or value to focus on. They are different types of hierarchies existing in datawarehousing. Though all the types of hierarchies are not supported by all the reporting tools most of them are supported with multiple hierarchy types. The most common hierarchies are

1)    Parent Child Hierarchies

2)    Skipped Level Hierarchies

3)    Ragged Hierarchies

4)    Level Based Hierarchy

We will try to understand about each and every hierarchy with some examples here.

Level –based Hierarchies:

Each level represents a position in the hierarchy. For example, a time dimension might have a hierarchy that represents data at the month, quarter, and year levels.
Each level above the base (or most detailed) level contains aggregate values for the levels below it. The members at different levels have a one-to-many parent-child relation. For example, Q1-05 and Q2-05 are the children of 2005, thus 2005 is the parent of Q1-05 and Q2-05.

About Parent-Child Hierarchies

The most common real-life occurrence of a parent-child hierarchy is an organizational reporting hierarchy chart, where the following all apply:

• Each individual in the organization is an employee.
• Each employee, apart from the top-level managers, reports to a single manager.
• The reporting hierarchy has many levels.

These conditions illustrate the basic features that define a parent-child hierarchy, namely:

• A parent-child hierarchy is based on a single logical table (for example, the "Employees" table)
• Each row in the table contains two identifying keys, one to identify the member itself, the other to identify the "parent" of the member (for example, Emp_ID and Manager_ID)

The following table shows how this parent-child hierarchy could be represented by the rows and key values in an Employees table.

Emp_ID	Manager_ID
Andrew	null
Barbara	Andrew
Carlos	Andrew
Dawn	Barbara
Emre	Barbara

About Levels and Distances in Parent-Child Hierarchies

Unlike the situation with level-based hierarchies, all the dimension members of a parent-child hierarchy occur in a single logical column. In a parent-child hierarchy, the parent of a member is in another row in the same logical column, pointed to by the parent key. This is unlike a level-based hierarchy, where the parent of a member is in a different logical column in the same row. In other words, navigation in a parent-child hierarchy follows data values, while navigation in a level-based hierarchy follows the metadata structure.

About Parent-Child Relationship Tables

The parent-child relationship table must include four columns, as follows:

• A column that identifies the member
• A column that identifies an ancestor of the member

Note: The ancestor may be the parent of the member, or a higher-level ancestor.

• A "distance" column that specifies the number of parent-child hierarchical levels from the member to the ancestor
• A "leaf" column that indicates if the member is a leaf member (1=Yes, 0=No)

The column names can be user defined. The data types of the columns must satisfy the following conditions:

• The member and ancestor identifier columns have the same data type as the associated columns in the logical table that contains the hierarchy members.
• The "distance" and "leaf" columns are INTEGER columns.

Note the following about the rows in a parent-child relationship table:

• Each member must have a row pointing at itself, with distance zero.
• Each member must have a row pointing at each of its ancestors. For a root member, this is a termination row with null for the parent and distance values.

Member_Key	Ancestor_Key	Distance	Isleaf
Andrew	Andrew	0	0
Barbara	Barbara	0	0
Carlos	Carlos	0	0
Dawn	Dawn	0	0
Emre	Emre	0	0
Andrew	null	null	0
Barbara	Andrew	1	0
Carlos	Andrew	1	1
Dawn	Barbara	1	1
Dawn	Andrew	2	1
Emre	Barbara	1	1
Emre	Andrew	2	1

Unbalanced (or ragged) hierarchy. An unbalanced (or ragged) hierarchy is a hierarchy where the leaves (members with no children) do not necessarily have the same depth. For example, a site can choose to have data for the current month at the day level, previous months data at the month level, and the previous 5 years data at the quarter level.

User applications can use the ISLEAF function to determine whether to allow drilldown from any particular member. A missing member is implemented in the data source with a null value for the member value. All computations treat the null value as a unique child within its parent. Level-based measures and aggregate-by calculations group all missing nodes together.

Note that unbalanced hierarchies are not necessarily the same as parent-child hierarchies. Parent-child hierarchies are unbalanced by nature, but level-based hierarchies can be unbalanced also.

Skipped-level hierarchy. A skip-level hierarchy is a hierarchy where there are members that do not have a value for a particular ancestor level. For example, in a Country-State-City-District hierarchy, the city 'Washington, D.C.' does not belong to a State. In this case, you can drill down from the Country level (USA) to the City level (Washington, D.C.) and below.

In a query, skipped levels are not displayed, and do not affect computations. When sorted hierarchically, members appear under their nearest ancestors.

A missing member at a particular level is implemented in the data source with a null value for the member value. All computations treat the null value as a unique child within its parent. Level-based measures and aggregate-by calculations group all skip-level nodes together.

Hierarchy with Unbalanced and Skip-Level Characteristics