How to Add a Drill Across Definition in the Prophix 12 Web Client

Posted By
BDO

Prophix recently announced the release of the Web Client for Prophix 12. In this post I will discuss how to add a Drill Across definition in the Prophix 12 Web Client, but first, a few answers to questions you may be asking.

What is a Drill Across definition?

A Drill Across definition is a dataset drawn from a data source outside Prophix, typically a relational database. It is similar to a relational data view in the Prophix Smart Client. However, a Drill Across definition can only be created and used in the Prophix Web Client.

Who creates a Drill Across definition?

Administrators and advanced users.

Where is the Drill Across definition made?

Use Drill Across Designer to create relational data views.

Follow these simple steps to configure your Drill Across definition in Prophix:

1. Select a Data Connector

To define a Drill Across definition, start by selecting a data connector (administrators and advanced users create data connectors using the Prophix Smart Client). Only SQL Server can be used for the Drill Across data source.

How to Add a Drill Across Definition in the PROPHIX 12 Web Client

2. Define a data connection

  • Open the Prophix Web Client and select ‘Drill Across Designer’ from the Menu.

How to Add a Drill Across Definition in the PROPHIX 12 Web Client. 2

  • Click on the Folder label to select the desired Data Connector.

How to Add a Drill Across Definition in the PROPHIX 12 Web Client. 3

  • In the ‘Open’ window select the connector and click ‘Ok’.

How to Add a Drill Across Definition in the PROPHIX 12 Web Client. 4

  • Select the SQL table assigned to the selected connector holding the transactional data.

How to Add a Drill Across Definition in the PROPHIX 12 Web Client. 5

How to Add a Drill Across Definition in the PROPHIX 12 Web Client. 6

  • Click the ‘+’ sign to add a column that will be available to display in the ‘Display’ section, click Choose, and select a column. Repeat for each column you want to add.

How to Add a Drill Across Definition in the PROPHIX 12 Web Client. 7

  • Click ‘Choose’, and select a column.

How to Add a Drill Across Definition in the PROPHIX 12 Web Client. 8

  • Repeat for each column you want to add.

How to Add a Drill Across Definition in the PROPHIX 12 Web Client. 9

(Optional) Specify data connector settings:

To customize the behavior of the data connector, click Advanced. The optional settings are as follows:

  • Maximum records to retrieve

From 0 to 100,000, in increments of 500; set to 0 to return all records. Default setting is 1,000.

  • Connection timeout

From 0 to 3,600 seconds, in increments of 100. Default setting is 300.

  • SQL command timeout

From 0 to 3,600 seconds, in increments of 100. Default setting is 300.

How to Add a Drill Across Definition in the PROPHIX 12 Web Client. 10

How to Add a Drill Across Definition in the PROPHIX 12 Web Client. 11

3. Map the display columns

After setting up the data connection, choose the external columns you want to appear in the Drill Across definition and map them to the dimensions in your Prophix cube.

Open the drawer and click Display.

  • Open the column selector tool:

How to Add a Drill Across Definition in the PROPHIX 12 Web Client. 12

  • The columns you choose for the data connection are listed on the left. The list on the right is for the columns that you want to show in the Drill Across definition:

How to Add a Drill Across Definition in the PROPHIX 12 Web Client. 13

  • Highlight columns and use the arrow buttons to move them to the list on the right. When you finish selecting display columns, click OK:

How to Add a Drill Across Definition in the PROPHIX 12 Web Client. 14

  • Click Cube label to select the cube that contains the dimensions to map, and click OK:

How to Add a Drill Across Definition in the PROPHIX 12 Web Client. 15

How to Add a Drill Across Definition in the PROPHIX 12 Web Client.16

  • For each display column, do the following:
    • (Optional) Under Display Name, edit the name to read what you want users to see.
    • Under Dimension Map, click and select the dimension to map the column to.
    • Under Member Type, click and select an option for how the dimension member should be displayed in the drill-across results. For example, if you select Member plus children, the drill-across will show the associated data for the member, along with the data for its children
    • Alternately, you can map to a dimension through a table: under Mapping Table, click mapping label, browse to and select the mapping table file.

What is a mapping table? A mapping table matches external data fields to cube data fields. Typically a mapping table is needed when external keys are used to identify accounts or other dimension members (not the same as those used in a Prophix model). For example, a remote location in your organization may have a separate accounting system with its own chart of accounts. Mapping tables are created in Prophix Smart Client and stored as their own file type.

How to Add a Drill Across Definition in the PROPHIX 12 Web Client. 17

How to Add a Drill Across Definition in the PROPHIX 12 Web Client. 18

How to Add a Drill Across Definition in the PROPHIX 12 Web Client. 19

How to Add a Drill Across Definition in the PROPHIX 12 Web Client. 20

4. Define the data formats

After mapping the display columns, the final step in defining a Drill Across definition is to specify the formatting for the data.

  • Open the drawer and click Format, and format the data in a way which is useful to you:

How to Add a Drill Across Definition in the PROPHIX 12 Web Client. 21

  • To see the live data, click Preview button:

How to Add a Drill Across Definition in the PROPHIX 12 Web Client. 23

5. Final Steps

  • Save configured Drill Across Definition:

How to Add a Drill Across Definition in the PROPHIX 12 Web Client. 24

  • Save as TRANSACTIONAL Drill Across and click OK:

How to Add a Drill Across Definition in the PROPHIX 12 Web Client. 25

6. Testing

Now the Drill Across definition is ready to be used. To test it, open Ad Hoc analysis for Financial Model. Select the value that needs to be drilled across, right click on it and select Drill Across:

How to Add a Drill Across Definition in the PROPHIX 12 Web Client. 26

In the popped-up window select the TRANSACTIONAL Drill Across definition that we just configured:

How to Add a Drill Across Definition in the PROPHIX 12 Web Client. 27

Review the live result:

How to Add a Drill Across Definition in the PROPHIX 12 Web Client. 28


Which is better for your business? Download the whitepaper now to find out!
Which is better for your business? Download the whitepaper now to find out!

 

BDO eBook - The Cloud Changes the Game

There are times in the course of your business when you have the opportunity to dramatically accelerate growth and improve day-to-day efficiencies. Recognizing

Download