23 Analyze Data in the Web Interface

For convenience, you can perform analysis on cube data from the Essbase web interface.

To analyze data grids in the Essbase web interface,

1. Log in to Essbase with at least Database Access role for the application whose cube data you want to analyze.
2. On the Applications page, expand the application, and highlight the row containing the cube name.
3. From the Actions menu to the right of the cube name, click Analyze Data.

A grid is displayed in the Ad Hoc Analysis tab. In this tab, you can:

• Perform ad hoc analysis against the cube you selected when you opened the Analyze Data view.
• Save a grid layout that you can refresh when you use the Ad Hoc Analysis tab in the future.

On the Reports tab, you can use MDX to write sophisticated data queries to populate the grid and to save as named reports.

Perform Ad Hoc Analysis in the Web Interface

In the Ad Hoc Analysis tab of the Analyze Data view, a grid is displayed containing each of the base dimensions (non-attribute dimensions) from the cube.

You may or may not see data in the ad hoc grid, depending on your filter access and how data is stored in the cube. Data is not always stored at the topmost member for every dimension hierarchy.

Use the ad hoc navigation buttons at the top left of the Ad Hoc Analysis tab to navigate to data that you are allowed to see. If your filter grants you write permission on the cube, the Submit button enables you to update data for stored intersections within the scope of your filtered access.

Work with Layouts

If you create a grid that you would like to use again in the future, you can save it at any time as a Layout.

To create a layout,

1. In the Analyze Data view for your cube, on the Ad Hoc Analysis tab, create an ad hoc grid that you want to save.
2. Click Save Layout.
3. Enter a name for your layout, and optionally, a description.
4. If you want to see this grid each time you analyze data, instead of the database default ad hoc query, check the default layout box.
5. Click Save.

The last ad hoc grid that was rendered during your session will be displayed the next time you log in, unless a default is set.

To view a grid previously saved as a layout,

1. If layouts are not listed by name in the Ad Hoc Analysis tab, click the Layouts Panel button to display the list.
2. Click the name of a stored layout to render it in the grid.
   
   
   
   

To return to the default ad hoc view, click the Reset Layout button.

To delete or edit layouts that you created, use the Actions menu next to the layout name. The Edit option allows you to select the layout as your default, update the description, or remove the default setting on a layout previously set as your default.

Access to Layouts

How you work with layouts depends on your cube access.

Clicking on a saved layout name causes it to render data in the Ad Hoc Analysis tab of the Analyze Data view.

Users with, at minimum, the application-level role of Database Manager can:

• See and render layouts created by others for this cube.
• Designate a layout to be the database default. This layout is shown to all cube users when they analyze data, unless they have previously created their own user default layouts.
• Delete layouts created by any user of this cube.

Layouts and reports are included when the cube is copied or moved using migration, export, and Lifecycle Mangement (LCM) tools.

Analyze and Manage Data with MDX

MDX (Multidimensional Expressions) is a powerful data manipulation and querying language.

With MDX, you can:

• Query and report against data and metadata in Essbase cubes

• Insert data into an Essbase cube

• Export data from an Essbase cube

An MDX query is a single MDX statement, having exactly one result set, that applies to a single cube.

An MDX report is a single MDX query, saved in the cube context. You can access MDX reports from Smart View and from the Essbase web interface.

An MDX script is a file, with an .mdx extension, that you can upload and then run from Jobs or in Smart View. Only MDX Insert and Export statements should be used in MDX scripts. To analyze grid data, use MDX reports rather than MDX scripts.

Topics:

• Analyze Data with MDX Reports

• Insert and Export Data with MDX

• Run MDX Scripts

Analyze Data with MDX Reports

You can store and render queries in the Essbase web interface using MDX reports. The minimum permission required to create a report is Database Manager.

Defining Layouts using the Ad Hoc Analysis tab may not always be the most efficient way to create a sophisticated report. If you know exactly what you want to query, you can use MDX to create a query to populate the grid.

To create an MDX report:

1. Log in to the Essbase web interface as a Database Manager or higher role.
2. On the Applications page, expand an application and select a cube.
3. Click the Actions menu to the right of the cube name, and select Analyze Data.
4. In the Analyze view, select the Reports tab and click Create.
5. Enter a name for the report, and optionally, a description.
6. In the Query field, enter an MDX query relevant to the current cube. For example:

   SELECT
     {([West].children)}
   ON COLUMNS,
     {([Diet].children)}
   ON ROWS
   

   The query must contain both row and column axes specifications. In other words, the query syntax must include specifications for both ON COLUMNS and ON ROWS, even if only an empty set {} is specified for one axis.

   Because the context of Analyze Data is the active cube, we recommend that you omit the optional cube specification (the FROM clause) from MDX reports. Omitting the FROM clause allows for more flexibility—if the cube is copied or renamed, the report will work in the new cube.

   Substitution variables are supported in MDX reports, but not runtime substitution variables. To use runtime substitution variables, save the MDX query as a script, and run it from Smart View using Calculate on the Essbase ribbon.

7. Click Validate to verify your MDX syntax, and then click Save.
8. From the Reports panel on the left, select the saved report to render a grid.

To learn more about MDX, see MDX and Writing MDX Queries.

Access to MDX Reports

How you work with reports depends on your cube access.

Users with, at minimum, the application-level role of Database Access can render saved MDX reports created by others. The data a user sees displayed in the report depends on that user's filter access.

In addition to rendering saved reports, Database Access users can export result sets in various formats: HTML, CSV, Excel, and JSON.

Database Access users can also view the MDX query that defines the report, by clicking the Actions menu next to the report name and selecting View.

If you have at least Database Manager role, you can use reports in the same ways that Database Access users can. Additionally, you can edit and delete reports using the Actions menu.

If you are a Service Administrator, you can additionally use the Execute As button to impersonate other users and check their data access. This can be useful for testing filters assigned to various users.

Examples of MDX Reports

The MDX examples in this section demonstrate special types of analyses you can perform, using MDX reports, that are not easily accomplished in the Ad Hoc Analysis view.

The following examples are designed to work on the Sample Basic cube.

Metadata Report

The following example returns only metadata (member names, but no data):

SELECT 
  {[Product].Levels(1).Members} 
ON ROWS, 
  {} 
ON COLUMNS

returning the grid:

Attribute Report

The following example uses, on columns, members from an attribute dimension:

SELECT 
 [Product].Children 
ON ROWS, 
 [Ounces].Children 
ON COLUMNS 
WHERE {Sales}

returning the grid:

Filtered Report

The following example uses a slicer (WHERE clause) to limit the query to Cola. Additionally, the Filter function limits the level 0 markets in the query to those that have a negative profit.

SELECT
  { Profit } 
ON COLUMNS,
  Filter( [Market].levels(0).members, Profit < 0) 
ON ROWS
WHERE {Cola}

returning the grid:

UDA Report

The following example shows Product data for Market dimension members that have a user defined attribute (UDA) of "Major Market." A slicer (WHERE clause) limits the query to include only Sales data.

SELECT 
  [Product].Children 
ON ROWS, 
  {Intersect(UDA([Market], "Major Market"), [Market].Children)} 
ON COLUMNS 
WHERE {Sales}

returning the grid:

Insert and Export Data with MDX

In addition to being useful for grid-based analysis, MDX also enables you to copy and update subsets of multidimensional data.

The MDX Insert clause enables you to update the cube with data, either from another cube or from a calculated (non-physical) member that you define using MDX.

The MDX Export clause enables you to save and export query results as data subsets that you can view or import later.

Insert and Export MDX statements can be run as saved MDX scripts.

To learn more about MDX Insert and Export, see MDX Insert Specification and MDX Export Specification.

Run MDX Scripts

Use MDX scripts when you need to execute Insert or Export data operations.

For analysis of grid data, use MDX reports. See Analyze Data with MDX Reports.

To use MDX scripts, select a workflow:

• Write, Upload, and Run an MDX Script
• Write an MDX Script in the Script Editor and Run It
• Create an MDX Script in Cube Designer and Run it

Write, Upload, and Run an MDX Script

Use this workflow to write MDX scripts in a text editor and upload them to Essbase.

1. Write the MDX script in a text editor, and save it with an .mdx extension.

2. Upload the MDX script to the application or cube directory under Files in the Essbase web interface.

3. Run the MDX script from Jobs or from Smart View, using Calculate on the Essbase ribbon.

Write an MDX Script in the Script Editor and Run It

Use this workflow to write MDX scripts in a script editor on the cube, and run them from Jobs.

1. On the Applications page, expand an application and cube.

2. From the cube's Actions menu, click Inspect.

3. Click Scripts, and then click MDX Scripts.

4. Click + to open a script editor.

5. Write the MDX script. A member tree and function list can help you.

6. Validate and save the script, then close the script editor.

7. Run the MDX script from Jobs (see Run MDX), or if using Smart View, using Calculate on the Essbase ribbon.

Create an MDX Script in Cube Designer and Run it

Use this workflow to create MDX scripts using an application workbook, and run them from Jobs.

1. In an application workbook, create an MDX worksheet. See Work with MDX Worksheets in Cube Designer.
2. Add a file name in the File Name field.
3. Indicate, in the Execute MDX field, whether to execute the MDX at the time the cube is created. Valid entries are Yes and No.
4. Add the MDX script below the Script line.
5. Save the application workbook.
6. Build the cube. See Create an Application and Cube in Cube Designer.
7. Run the MDX script from Jobs, or if using Smart View, using Calculate on the Essbase ribbon.

Guidelines for MDX Scripts

Use the following guidelines when working with MDX scripts.

• Use MDX scripts to perform Insert or Export data operations.

• For grid analysis, use MDX reports instead of MDX scripts.

• MDX scripts can optionally include runtime substitution variables.

  • To be usable within Smart View, MDX scripts with runtime substitution variables must use the XML syntax within the SET RUNTIMESUBVARS calculation command, including <RTSV_HINT>.

  • To set a runtime substitution variable so that it calculates only the visible slice of data in Smart View, set the value of the runtime substitution variable to POV, and set the data type to member.

  • When run from the Essbase web interface, your MDX scripts may use substitution variables, but not runtime substitution variables. To use runtime substitution variables in MDX scripts, you must run the scripts from Smart View, using Calculate on the Essbase ribbon.

Use Substitution Variables

Examples of MDX Scripts

The following are examples of MDX scripts you can run on the Sample Basic cube, either from Jobs or in Smart View.

MDX Insert

You can save this .mdx script and run it from Jobs or from the Calculate dialog in Smart View.

INSERT "([Measures].[Payroll])" TO "([Measures].[Revised_Payroll])"
INTO [Sample].[Basic]
FROM (
       SELECT 
            {[Measures].[Payroll]} ON COLUMNS,
                {Crossjoin
                  (Crossjoin(Descendants([Year]), 
                   Crossjoin(Descendants([Scenario]),
                  Descendants([Product]))),
                  Descendants([Market]))} ON ROWS
       FROM [Sample].[Basic]
);

The above example assumes you have previously added a Revised_Payroll measure to Sample Basic.

MDX Export

You can save this .mdx script and run it from Jobs or from the Calculate dialog in Smart View.

EXPORT INTO FILE "sample01" OVERWRITE
SELECT 
 {[Mar],[Apr]} 
ON COLUMNS,
 Crossjoin({[New York]},
  Crossjoin({[Actual],[Budget]},
     {[Opening Inventory],[Ending Inventory]})) 
ON ROWS
FROM [Sample].[Basic]
WHERE ([100-10])

After you run the script, the following export file, sample01.txt, is saved in the cube directory of the file catalog:

Market,Scenario,Measures,Mar,Apr
New York,Actual,Opening Inventory,2041,2108
New York,Actual,Ending Inventory,2108,2250
New York,Budget,Opening Inventory,1980,2040
New York,Budget,Ending Inventory,2040,2170

MDX Export Using Runtime Substitution Variable

You can save this .mdx script and run it from the Calculate dialog in Smart View.

SET RUNTIMESUBVARS
{
 States = "Massachusetts"<RTSV_HINT><svLaunch>
                    <description>US States</description>
                    <type>member</type>
                    <allowMissing>false</allowMissing>
                    <dimension>Market</dimension>
                    <choice>multiple</choice>
                    </svLaunch></RTSV_HINT>;
};
EXPORT INTO FILE "sample002" OVERWRITE
SELECT
 {[Mar],[Apr]}
ON COLUMNS,
 Crossjoin({&States}, Crossjoin({[Actual],[Budget]},
 {[Opening Inventory],[Ending Inventory]}))
ON ROWS
FROM [Sample].[Basic]
WHERE ([100-10])

After you run the script, the following export file, sample002.txt, is saved in the cube directory of the file catalog:

Market,Scenario,Measures,Mar,Apr
Massachusetts,Actual,Opening Inventory,-54,-348
Massachusetts,Actual,Ending Inventory,-348,-663
Massachusetts,Budget,Opening Inventory,-160,-520
Massachusetts,Budget,Ending Inventory,-520,-910