Introduction to Essbase Drill Through

When Smart View users need more information than what is available in the cube, drill through reports can provide customized access to external source data.

Typically, given the detailed granularity of data kept in data warehouses and other source data systems, the volume of external data can be too large to be useful for analysis. To populate an Essbase cube with the optimal amount of data for analysis, a common practice is to aggregate the source data (for example, aggregating daily transactional values to weekly or monthly totals), and load this aggregate data to the Essbase cube.

During subsequent analyses of the data in Essbase, if Smart View users find anomalies worthy of investigation, drill through provides them a way to quickly view the underlying source data to search for the cause. For example, if August data is unexpectedly different from July, users can drill through to the source system to find which record(s) may have been responsible.

To provide analytical Smart View users additional information about what constitutes one or more data values in the cube, database managers can implement drill through reports to give more depth of insight into the source data before it is rolled up and loaded into Essbase.

Drill through reports are one way you can build a data exchange interface between Essbase and external source systems.

Consider a relational table, SB_DT, with all records selected. The SQL query is:

SELECT * FROM SB_DT

The query result (in SQL Developer) is truncated for length in this illustration, as there are thousands of records in the table:

View of tabular data in SQL Developer. Thousands of rows with column DIMENSION_PRODUCT containing values for Product SKUs, column DIMENSION_MARKET containing U.S. states, column DIMENSION_YEAR containing months, column DIMENSION_SCENARIO containing Actual or Budget, and columns of numbers named SALES, COGS, MARKETING, and PAYROLL.

Consider the same table with fewer records selected. For example, if the SQL selection is narrowed down to named columns, measures are aggregated, and a filter (WHERE clause) is applied,

select DIMENSION_PRODUCT, DIMENSION_MARKET, YEAR_PARENT, DIMENSION_SCENARIO, sum(SALES) as SALES, sum(COGS) as COGS
         from SB_DT where DIMENSION_SCENARIO ='Actual' AND DIMENSION_MARKET ='California' AND YEAR_PARENT ='Qtr4' group by DIMENSION_PRODUCT, DIMENSION_MARKET,
        YEAR_PARENT, DIMENSION_SCENARIO

then the query result is aggregated and filtered:

View of tabular data in SQL Developer. 12 rows with column DIMENSION_PRODUCT containing values for Product SKUs, column DIMENSION_MARKET containing California, column YEAR_PARENT containing Qtr4, column DIMENSION_SCENARIO containing Actual, and columns of numbers named SALES and COGS.

You can harness the power of RDBMS queries in Essbase, using Datasources, data load, and drill through reports. Drill through reports provide filtered access to an external source of data directly from an Essbase query in a Smart View worksheet.

Drill Through Terminology

This topic explains the meaning of terms related to Essbase drill through.

Drill through (verb)

To drill through is to access external data from one or more Essbase cell intersections in a Smart View worksheet. The drill through action provides additional information not contained in the Essbase cube. The need to drill through arises when Essbase contains aggregated (“rolled up”) values and the external source system has more granular data that can be made available.

If a query is performed upon drill through, the results display in a new worksheet that opens -- this is the drill through report. The report contains information pulled from the external source data.
If a URL is launched upon drill through, it opens in a Web browser. Parameters can be passed to the URL, to execute a customized search on the website.

Drill through report

A drill through report is the result of a drill through operation, performed from a Smart View grid, to additional data from a source system external to Essbase.

Drill through report definition

A drill through report definition is the way, if you are a Database Manager or higher, to define the access your users should have to external information. You create drill through report definitions in the Essbase web interface or REST API. They are associated with your cube. As part of the definition, you specify:

A column mapping. This specifies which external columns you want displayed in the reports, and how much hierarchical (generational) depth of access you want to provide (for example, do you want to reveal daily, monthly, or quarterly information from the external source?)
A drillable region. This specifies which cell intersections of your cube offer access to drill through reports (or a URL) containing additional external data. In the examples to follow, drillable regions in the POV of the Smart View worksheet are color coded as blue, using cell styles. You specify drillable regions using any of the Member Set Functions available in Essbase. In the examples to follow, the drillable region is @DESCENDANTS("Measures") on Sample Basic.
A mapping for runtime parameters, if a parameterized query is implemented in the underlying Datasource query (optional).

Drill through report definitions usually rely on predefined connections and Datasources in Essbase (unless you are defining access to a file uploaded to Essbase). A connection stores the authentication details to the external source. One or more Datasources you define over the connection enable you to specify an initial query to fetch from the external source (for example, selecting all from a particular table). The query you specify in the Datasource can fetch as large or small a subset of data as you want to start with. Later, you narrow down how much data access to provide, when you create or edit the drill through report definition.

Workflow for Drill Through Report Design

As a database manager, use the following workflow to design and test drill through for your cube.

Prepare data access
1. Upload a data file, OR
2. Obtain authorization information needed to access an external source system
3. Define a connection and one or more Datasources to the data file or to the external source
Create drill through report definitions on the cube
1. define the column mapping
2. define the drillable region
3. map runtime parameters, if used
Test the drill through reports
1. Prepare Smart View
  - install the latest version
  - connect to the cube
  - enable cell styles to show drillable regions
2. Drill through from different cell intersections in Smart View
  - drill through from one intersection
  - drill through from multiple intersections
  - drill through from cells at different generations
3. Check the drill through report outputs and the platform log

This workflow, and the rest of this introduction, emphasize what you need to know to design and test drill through access to external source data in an RDBMS. If you are more interested in drill through URL implementation, see Drill Through to a URL.

To understand the different access requirements for drill through design and use, see Access to Drill Through Reports.

How Drill Through Works

In this example, assume the Sample Basic cube in Essbase has Qtr1-Qtr4 as the lowest levels of the time dimension.

Year hierarchy. Year is the dimension name, with level 0 children Qtr1, Qtr2, Qtr3, and Qtr4.

There are no months in the outline hierarchy, but the monthly data is available in the external source system, in a table column named DIMENSION_YEAR:

DIMENSION_YEAR column with month values: Aug, Sep, Oct, etc

When a Smart View user drills through on a cell intersection of a Sales value for Qtr1:

Smart View grid with user selection on cell value 1998 at intersection of (Qtr1, Sales, New York, Actual, Cola)

The drill through report Essbase generates is

Drill through report from the source system returning only records where YEAR_PARENT=Qtr1, and revealing a SALES total of 1998

The drill through report shows additional information, from the source database, about Sales for Qtr1. Notice that the Jan, Feb, and Mar values add up to the value for Qtr1: 678+645+675=1998.

The query Essbase internally uses to build the above drill through report is:

SELECT
        "DIMENSION_PRODUCT", "DIMENSION_MARKET", "DIMENSION_YEAR", "DIMENSION_SCENARIO",
        "SALES", "COGS", "YEAR_PARENT"  FROM <Query defined in Datasource> WHERE
        "YEAR_PARENT" = 'Qtr1' AND "DIMENSION_PRODUCT" = '100-10' AND "DIMENSION_MARKET" =
        'New York' AND "DIMENSION_SCENARIO" = 'Actual'

From the platform log, administrators can access the exact queries behind each drill through report.

Drill Through Report Definition

A drill through report definition is the way, if you are a Database Manager or higher, to define the access your users should have to external information.

To enable the drill through report generated in How Drill Through Works, the database manager created a drill through report definition associated with Sample Basic. The drill through report references a predefined Datasource that uses a query to pull external data from SB_DT (the hypothetical source system table mentioned in Introduction to Essbase Drill Through).

In the drill through report definition, the database manager specified the following column mapping:

External column	Include in report	Essbase dimension	Gen/Lev filter
DIMENSION_PRODUCT	Y	Product	Product SKU [Generation]
DIMENSION_MARKET	Y	Market	State [Generation]
DIMENSION_YEAR	Y	Year	None
DIMENSION_SCENARIO	Y	Scenario	Level0 [Level]
SALES	Y	None	--
COGS	Y	None	--
YEAR_PARENT	Y	Year	Quarter [Generation]

The column mapping defines which external source columns should be included in the report, which Essbase dimensions those columns map to, and (optionally) a generation/level filter condition indicating how much depth of access to provide.

In the mapping template shown above, the database manager:

Mapped the external DIMENSION_PRODUCT column to the generation named Product SKU in the Essbase Product dimension. This type of column mapping is called generation mapping.
Mapped the external DIMENSION_MARKET column to the generation named State in the Essbase Market dimension (this is another example of generation mapping).
Mapped the external DIMENSION_YEAR column to the Year dimension, with no further filter. This type of column mapping is called dimension mapping.
Mapped the external DIMENSION_SCENARIO column to the lowest level (level 0) of the Essbase Scenario dimension. This type of column mapping is called level 0 mapping.
Did not map the columns SALES and COGS to anything, but selected to include these columns in the report. It is not typically necessary to map columns to the Essbase accounts dimension.
Mapped the external YEAR_PARENT column to the generation named Quarter in the Year dimension.

Column Mapping Options

Because the query that Essbase generates to pull data from your Datasource is highly dependent on your defined column mapping, it is helpful to understand the different ways of mapping columns and in which cases each method is useful. The types of column mapping are:

Dimension mapping
Generation mapping
Level 0 mapping

Dimension Mapping

With Dimension mapping, you map a source data column directly to a dimension name in the Essbase cube. This type of mapping is most useful when the source data column contains all the layers of data represented in the corresponding dimension of your cube.

For example, if a source data column MONTH contains a mixture of all the same generations/levels that the dimension has, as shown,

Side by side view of a truncated relational column, MONTH, next to a truncated Year hierarchy from Sample Basic. MONTH column contains records: Jan, Qtr3, Feb, Qtr1, Year, Aug, etc. Year hierarchy contains Qtr1 with children Jan, Feb, Mar, Qtr2 with children Apr, May, Jun, etc.

then it makes sense to map the MONTH column to the Essbase Year dimension, with no further filter:

External column	Include in report	Essbase dimension	Gen/Lev filter
MONTH	Y	Year	None

When you use a Dimension mapping for MONTH as shown above, the filter condition (the WHERE clause of the SQL query) is not predefined for the MONTH column:

SELECT "MONTH"
FROM <Query defined in Datasource>
WHERE "MONTH" = '<Grid context>'

and the drill through result for MONTH will return the current Smart View cell intersection.

Learn more about how dimension mapping works in Drill Through Use Case Example.

Generation Mapping

With Generation mapping, you map a source data column to a named generation in an Essbase dimension. This type of mapping is useful when the source data column contains only the data layer represented in a specific generation of a dimension in the cube. For example, if the source data column MONTH contains only months, and the Year dimension has months at generation 3,

Side by side view of a truncated relational column, MONTH, next to a truncated Year hierarchy from Sample Basic. MONTH column contains only months as records: Jan, Feb, Feb, Mar, Mar, Apr, May, etc. Year hierarchy contains Qtr1 with children Jan, Feb, Mar, Qtr2 with children Apr, May, Jun, etc.

then the best choice is to map the source column to generation 3 (Months) of the Year dimension:

External column	Include in report	Essbase dimension	Gen/Lev filter
MONTH	Y	Year	Months [Generation]

When you use a Generation mapping for MONTH as shown above, the query filter condition will be predefined for the MONTH column:

SELECT "MONTH"
FROM <Query defined in Datasource>
WHERE "MONTH" = '<Generation filter>'

and the drill through result for MONTH will return values down to the Months generation of the Year dimension. No data (if it exists) will be returned for any level lower than Months.

Generation mapping is not ideal for asymmetric (ragged) hierarchies. A generation mapping has no effect on the drill through query in a dimension with a ragged hierarchy unless the drill through is performed on a member in a direct ancestral line to the generation where the column mapping is defined. To avoid unexpected results, Oracle recommends using level 0 mapping rather than generation mapping for drilling through on asymmetric hierarchies.

Learn more about how generation mapping works in Drill Through Use Case Example.

Level 0 Mapping

With level 0 mapping, Essbase adds to the filter condition all leaf level members from the hierarchy below the cell intersection (whichever member is selected in the current Smart View grid context at runtime).

Level 0 mapping is useful when working with asymmetric (ragged) hierarchies. In a ragged hierarchy, same-level (L) members do not all share the same generational (G) depth in the outline.

Ragged hierarchy member tree illustration. Measures, the member at the top, is generation 1 and is both level 2 and level 3. Children of Measures, Profit, Inventory, and Ratios, are generation 2 and levels 1 and 2. Members a through h are children of Profit, Inventory, and Ratios, and are generation 3 and levels 0 and 1. Members aa, ab, ba, bb, and bc are children of a and b, and are generation 4, level 0.

A common example of a ragged hierarchy is an employee organization structure.

The Product dimension of the Essbase cube outline for Sample Basic would be a ragged hierarchy if a few child products were added below 100-10 (Cola):

View of a truncated Product hierarchy from modified Sample Basic. Contains product 100 with children 100-10, 100-20, 100-30, etc. 100-10 is expanded to show added child members 100-10-10, 100-10-20, 100-10-30, and 100-10-40.

If the database manager maps the PRODUCT source column to level 0 of the Product dimension, as shown:

External column	Include in report	Essbase dimension	Gen/Lev filter
PRODUCT	Y	Product	Level0 [Level]

then the query filter condition will be predefined for the PRODUCT column:

SELECT "PRODUCT"
FROM <Query defined in Datasource>
WHERE  "PRODUCT" = <Level0> below <Grid context>

and the drill through result for PRODUCT will return all level 0 members below the Product member selected in the current Smart View cell intersection.

Learn more about how level 0 mapping works in Drill Through Use Case Example.

Drill Through Use Case Example

For this example use case, we will examine the following factors that you need to consider when designing drill through report access for Smart View users:

An Essbase cube for analysis
An external source system for drill through
A drill through report definition with column mapping defined by the database manager
Drill through report results from Smart View

Essbase Cube

In this example, the basis is a cube similar to the Essbase demo cube, Sample Basic, but with only quarterly level data for the time dimension present in the cube (months were removed). Assume the Year dimension has Qtr1-Qtr4 as its lowest (level 0) members:

Though months are lacking in this outline hierarchy, the monthly data is available externally by drilling through to the source system (assume it is Oracle Database), and accessing information from a column named DIMENSION_YEAR:

For the remaining dimensions, assume they are the same as in the Essbase demo cube Sample Basic that is available in the gallery section of the Files catalog. A quick review of these is in order:

The Measures dimension tracks key performance indicators for accounts, using dynamic calculations with Essbase calc formulas.

Measures dimension with generation 2 members Profit, Inventory, and Ratios. Profit is expanded to show child members Margin and Total Expenses. Margin is expanded to show child members Sales and COGS. Inventory and Ratios are not expanded but each has additional child members.

The Product dimension tracks the active inventory, descending two generations in depth, to generation 2 named Category (populated by 100 [alias Colas], 200, 300, 400, and Diet) and generation 3/level 0 named Product SKU (populated by 100-10 [alias Cola], 100-20, etc).

Product dimension with generation 2 members 100, 200, 300, 400, and Diet. Product category member 100 is expanded to show level 0 child members of Product SKUs: 100-10, 100-20, and 100-30. Other product category members, 200, 300, 400 and Diet, are not expanded but each has additional child members.

The Market dimension provides geographical separation with two additional generations below the dimension name. Generation 2 is Region (East, West, etc) and generation 3 is State.

Market dimension with generation 2 members East, West, South, and Central. Market region member East is expanded to show level 0 child members of some eastern U.S. states: New York, Massachusetts, Florida, Connecticut, and New Hampshire. Other product category members, West, South, and Central, are not expanded but each has additional child members.

The Scenario dimension adds financial reporting analysis to the cube with its two stored and two Dynamic Calc members:

Scenario dimension with generation 2 members Actual, Budget, Variance, and Variance %. The latter two members are Dynamic Calc, while Actual and Budget are stored.

External Source System

For the source system in this example, assume it is Oracle Database. The predefined Datasource in Essbase includes a SQL query that pulls information from a table in Oracle Database.

Our task as the database manager is to design a drill through report definition, based on this Datasource, that provides Smart View users the correct access to source system data pulled through the Datasource.

The query in the Datasource can be as simple as

SELECT * FROM TABLENAME

or it can be refined to pull any aggregation or assortment of the external data that you want to use as a basis.

The selection from our hypothetical table in Oracle Database includes external columns like those illustrated in our introduction. We will map some of these external columns to Essbase dimensions when we design the drill through report definition.

Column Mapping Definition

The column mapping in this example utilizes dimension mapping for Products, generation mapping for Year and Scenario, and level 0 mapping for Market.

External column	Include in report	Essbase dimension	Gen/Lev filter
DIMENSION_PRODUCT	Y	Product	None
DIMENSION_MARKET	Y	Market	Level0 [Level]
YEAR_PARENT	Y	Year	Quarter [Generation]
DIMENSION_SCENARIO	Y	Scenario	Scen [Generation]
SALES	Y	None	--
COGS	Y	None	--
MARKETING	Y	None	--
PAYROLL	Y	None	--
MISC	Y	None	--

Drill Through Report Examples by Column Mapping Type

The following drill through report examples illustrate the query results for each column mapping type that the database manager specifies as part of the drill through report definition.

Dimension Mapping Example 1

Using dimension mapping for Product with no hierarchical filter,

External column	Include in report	Essbase dimension	Gen/Lev filter
DIMENSION_PRODUCT	Y	Product	None

drill through performed from a cell intersection will be unbound to any specific generation or level.

Therefore, drilling through from (Year, Sales, West, Actual, Cola):

Smart View grid with user selection on cell value 14862 at intersection of (Year, Sales, West, Actual, Cola)

returns a drill through report filtered by the current grid context for Product, which happens to be 100-10 (100-10 is the Product SKU associated with the alias name Cola). All values pulled from the DIMENSION_PRODUCT column in the source system will be records where DIMENSION_PRODUCT = 100-10.

Drill through report with records only specific to Product SKU 100-10, revealing a SALES total of 14862

To validate drill through reports as you test them, check that the sum for the measure in the report matches the cell intersection upon which drill through was performed. In the example above, the drill through report is validated, because the cell drilled upon matches the value (14862) of the sum of the mapped column in the drill through report.

The query Essbase uses to build the above drill through report is:

SELECT "DIMENSION_PRODUCT", "DIMENSION_MARKET", "YEAR_PARENT", "DIMENSION_SCENARIO", "SALES", "COGS", "MARKETING", "PAYROLL", "MISC"
FROM <Query defined in Datasource>
WHERE (
"YEAR_PARENT" = 'Qtr3' OR
"YEAR_PARENT" = 'Qtr4' OR
"YEAR_PARENT" = 'Qtr1' OR
"YEAR_PARENT" = 'Qtr2') 
AND 
"DIMENSION_PRODUCT" = '100-10'
AND (
"DIMENSION_MARKET" = 'Oregon' OR
"DIMENSION_MARKET" = 'California' OR
"DIMENSION_MARKET" = 'Washington' OR
"DIMENSION_MARKET" = 'Utah' OR
"DIMENSION_MARKET" = 'Nevada')
AND 
"DIMENSION_SCENARIO" = 'Actual'

Dimension Mapping Example 2

Continuing from the previous example, let’s explore what happens when drilling through on Product at a higher level.

Drilling through from (Year, Sales, West, Actual, Colas):

Smart View grid with user selection on cell value 28306 at intersection of (Year, Sales, West, Actual, Colas)

returns a drill through report filtered by the current grid context for Product, which now happens to be 100 (100 is the Product category associated with the alias name Colas). All values pulled from the DIMENSION_PRODUCT column in the source system will be records where DIMENSION_PRODUCT = 100.

Drill through report with records only specific to Product category 100, revealing a SALES total of 23806

The drill through report is validated, because the cell drilled upon matches the value (23806) of the sum of the mapped column in the drill through report.

The query Essbase uses to build the above drill through report is:

SELECT "DIMENSION_PRODUCT", "DIMENSION_MARKET", "YEAR_PARENT", "DIMENSION_SCENARIO", "SALES", "COGS", "MARKETING", "PAYROLL", "MISC"
FROM <Query defined in Datasource>
WHERE (
"YEAR_PARENT" = 'Qtr3' OR
"YEAR_PARENT" = 'Qtr4' OR
"YEAR_PARENT" = 'Qtr1' OR
"YEAR_PARENT" = 'Qtr2') 
AND 
"DIMENSION_PRODUCT" = '100'
AND (
"DIMENSION_MARKET" = 'Oregon' OR
"DIMENSION_MARKET" = 'California' OR
"DIMENSION_MARKET" = 'Washington' OR
"DIMENSION_MARKET" = 'Utah' OR
"DIMENSION_MARKET" = 'Nevada')
AND 
"DIMENSION_SCENARIO" = 'Actual'

Generation Mapping Example 1

Using the generation mapping for Year with a filter on the generation named Quarter,

External column	Include in report	Essbase dimension	Gen/Lev filter
YEAR_PARENT	Y	Year	Quarter [Generation]

drill through performed from a cell intersection will be bound to the specified generation of Year.

Drilling through from (Qtr2, Sales, Market, Actual, Cola):

Smart View grid with user selection on cell value 16048 at intersection of (Qtr2, Sales, Market, Actual, Cola)

returns a drill through report filtered by the mapped generation context for Year, which is Quarters. Because Qtr2 is in the selected grid context, all values pulled from the YEAR_PARENT column in the source system will be records where YEAR_PARENT = Qtr2.

Drill through report with records only specific to Qtr2, revealing a SALES total of 16048

The drill through report is validated, because the cell drilled upon matches the value (16048) of the sum of the mapped column in the drill through report.

The query Essbase uses to build the above drill through report is:

SELECT "DIMENSION_PRODUCT", "DIMENSION_MARKET", "YEAR_PARENT", "DIMENSION_SCENARIO", "SALES", "COGS", "MARKETING", "PAYROLL", "MISC"
FROM <Query defined in Datasource>
WHERE 
"YEAR_PARENT" = 'Qtr2'
AND
"DIMENSION_PRODUCT" = '100-10'
AND (
"DIMENSION_MARKET" = 'Oregon' OR 
"DIMENSION_MARKET" = 'New York' OR 
"DIMENSION_MARKET" = 'Oklahoma' OR 
"DIMENSION_MARKET" = 'California' OR 
"DIMENSION_MARKET" = 'Florida' OR 
"DIMENSION_MARKET" = 'Washington' OR 
"DIMENSION_MARKET" = 'Utah' OR 
"DIMENSION_MARKET" = 'Iowa' OR 
"DIMENSION_MARKET" = 'New Mexico' OR 
"DIMENSION_MARKET" = 'Massachusetts' OR 
"DIMENSION_MARKET" = 'Texas' OR 
"DIMENSION_MARKET" = 'Illinois' OR 
"DIMENSION_MARKET" = 'Colorado' OR 
"DIMENSION_MARKET" = 'Connecticut' OR 
"DIMENSION_MARKET" = 'New Hampshire' OR 
"DIMENSION_MARKET" = 'Missouri' OR 
"DIMENSION_MARKET" = 'Louisiana' OR 
"DIMENSION_MARKET" = 'Ohio' OR 
"DIMENSION_MARKET" = 'Wisconsin' OR 
"DIMENSION_MARKET" = 'Nevada')
AND
"DIMENSION_SCENARIO" = 'Actual'

Generation Mapping Example 2

Continuing from the previous example, let’s explore what happens when drilling through on Year dimension at a higher level.

Drilling through from (Year, Sales, Market, Actual, Cola):

Smart View grid with user selection on cell value 62824 at intersection of (Year, Sales, Market, Actual, Cola)

returns a drill through report filtered by the mapped Quarters generation of Year. Values pulled from the YEAR_PARENT column in the source system will be Qtr1, Qtr2, Qtr3, and Qtr4.

Drill through report with records for all quarters of Year, revealing a SALES total of 62824

The drill through report is validated, because the cell drilled upon matches the value (62824) of the sum of the mapped column in the drill through report.

The query Essbase uses to build the above drill through report is:

SELECT "DIMENSION_PRODUCT", "DIMENSION_MARKET", "YEAR_PARENT", "DIMENSION_SCENARIO", "SALES", "COGS", "MARKETING", "PAYROLL", "MISC"
FROM <Query defined in Datasource>
WHERE (
"YEAR_PARENT" = 'Qtr3' OR 
"YEAR_PARENT" = 'Qtr4' OR 
"YEAR_PARENT" = 'Qtr1' OR 
"YEAR_PARENT" = 'Qtr2')
AND
"DIMENSION_PRODUCT" = '100-10'
AND (
"DIMENSION_MARKET" = 'Oregon' OR 
"DIMENSION_MARKET" = 'New York' OR 
"DIMENSION_MARKET" = 'Oklahoma' OR 
"DIMENSION_MARKET" = 'California' OR 
"DIMENSION_MARKET" = 'Florida' OR 
"DIMENSION_MARKET" = 'Washington' OR 
"DIMENSION_MARKET" = 'Utah' OR 
"DIMENSION_MARKET" = 'Iowa' OR 
"DIMENSION_MARKET" = 'New Mexico' OR 
"DIMENSION_MARKET" = 'Massachusetts' OR 
"DIMENSION_MARKET" = 'Texas' OR 
"DIMENSION_MARKET" = 'Illinois' OR 
"DIMENSION_MARKET" = 'Colorado' OR 
"DIMENSION_MARKET" = 'Connecticut' OR 
"DIMENSION_MARKET" = 'New Hampshire' OR 
"DIMENSION_MARKET" = 'Missouri' OR 
"DIMENSION_MARKET" = 'Louisiana' OR 
"DIMENSION_MARKET" = 'Ohio' OR 
"DIMENSION_MARKET" = 'Wisconsin' OR 
"DIMENSION_MARKET" = 'Nevada')
AND
"DIMENSION_SCENARIO" = 'Actual'

Level 0 Mapping Example

Using the level 0 mapping for Market with a filter on all level 0 (leaf) members,

External column	Include in report	Essbase dimension	Gen/Lev filter
DIMENSION_MARKET	Y	Market	Level0 [Level]

drill through performed from a cell intersection will return all the lowest members for Market.

Drilling through from (Year, Sales, Market, Actual, Cola):

returns a drill through report that includes the lowest level members of Market dimension, which happen to be States. All values pulled from the DIMENSION_MARKET column in the source system will be records containing States.

Drill through report with records for all leaf members (states) of Market, revealing a SALES total of 62824

The drill through report is validated, because the cell drilled upon matches the value (62824) of the sum of the mapped column in the drill through report.

The query Essbase uses to build the above drill through report is:

SELECT "DIMENSION_PRODUCT", "DIMENSION_MARKET", "YEAR_PARENT", "DIMENSION_SCENARIO", "SALES", "COGS", "MARKETING", "PAYROLL", "MISC"
FROM <Query defined in Datasource>
WHERE (
"YEAR_PARENT" = 'Qtr3' OR 
"YEAR_PARENT" = 'Qtr4' OR 
"YEAR_PARENT" = 'Qtr1' OR 
"YEAR_PARENT" = 'Qtr2')
AND
"DIMENSION_PRODUCT" = '100-10'
AND (
"DIMENSION_MARKET" = 'Oregon' OR 
"DIMENSION_MARKET" = 'New York' OR 
"DIMENSION_MARKET" = 'Oklahoma' OR 
"DIMENSION_MARKET" = 'California' OR 
"DIMENSION_MARKET" = 'Florida' OR 
"DIMENSION_MARKET" = 'Washington' OR 
"DIMENSION_MARKET" = 'Utah' OR 
"DIMENSION_MARKET" = 'Iowa' OR 
"DIMENSION_MARKET" = 'New Mexico' OR 
"DIMENSION_MARKET" = 'Massachusetts' OR 
"DIMENSION_MARKET" = 'Texas' OR 
"DIMENSION_MARKET" = 'Illinois' OR 
"DIMENSION_MARKET" = 'Colorado' OR 
"DIMENSION_MARKET" = 'Connecticut' OR 
"DIMENSION_MARKET" = 'New Hampshire' OR 
"DIMENSION_MARKET" = 'Missouri' OR 
"DIMENSION_MARKET" = 'Louisiana' OR 
"DIMENSION_MARKET" = 'Ohio' OR 
"DIMENSION_MARKET" = 'Wisconsin' OR 
"DIMENSION_MARKET" = 'Nevada')
AND
"DIMENSION_SCENARIO" = 'Actual'