Drillbridge Connector 1.0.0

October 31, 2024 - Version 1.0.0

The Drillbridge Connector is used to connect from the Dodeca client to the Drillbridge server and return a Dataset containing the results of a SQL query. This connector can use any Drillbridge report as the source for the Dataset, but the Dodeca client must provide with selector or member mappings to provide the proper query filter information to the Drillbridge server.

Once the Drillbridge Connector module has been imported into Dodeca, you can create a new Drillbridge Connection and create a new Drillbridge Dataset. Once the Dataset has been created, it can be used to retrieve data into a Universal Range in a Universal dataset.

Note: When the Drillbridge Connector is imported for the first time, the Dodeca client must be restarted before the module can be used.

Create a Drillbridge Connection

In the Connections metadata editor, select the New button to display the New Connection dialog. Be sure to choose the Type 'DrillbridgeConnection'.

image

Set the BaseUrl property to point to your Drillbridge server.

image

Once you have created the connection and commit the changes, you can then proceed to create the Dataset.

Create a Drillbridge Dataset

In this example, the Drillbridge report, which has the Drillbridge report alias supplier-detailed-transactions, uses the following SQL:

SELECT TRANSDATE,
       MARKET.STATE,
       PRODUCT.SKU_ALIAS + ' - ' + CAST(PRODUCT.OUNCES AS VARCHAR(2)) + ' oz. ' + PRODUCT.PKGTYPE as SKU,
       SUPPLIER.SUPPLIER_ALIAS + ' - ' + SUPPLIER.CITY + ', ' + SUPPLIER.STATE AS SUPPLIER,
       SALES.AMOUNT,
       YEARDIM.MONTH,
       YEARDIM.QUARTER,
       SALES.STATEID,
       YEARDIM.MONTHNUM,
       YEARDIM.QUARTERNUM,
       'TOTAL' AS TOTAL
FROM SALES INNER JOIN
     SUPPLIER ON SALES.SUPPLIERID = SUPPLIER.SUPPLIERID INNER JOIN
     YEARDIM ON YEARDIM.MONTHNUM = MONTH(SALES.TRANSDATE) INNER JOIN
     MARKET on MARKET.STATEID = SALES.STATEID INNER JOIN
     PRODUCT ON PRODUCT.PRODUCTID = SALES.PRODUCTID
WHERE TRANSDATE >= '1/1/2020'
AND TRANSDATE <= '1/1/2030'
AND SALES.STATEID IN ((SELECT STATEID FROM MARKET WHERE STATE IN {{"name":"Market", "expression":"#Market", "drillToBottom":"true", "sampleValue":"East"}}))
AND SALES.PRODUCTID IN ((SELECT PRODUCTID FROM PRODUCT WHERE SKU IN {{"name":"Product", "expression":"#Product", "drillToBottom":"true", "sampleValue":"Colas"}} OR SKU_ALIAS IN {{"name":"Product", "expression":"#Product", "drillToBottom":"true", "sampleValue":"Colas"}}))
AND SALES.SCENARIOID = (SELECT SCENARIOID FROM SCENARIO WHERE SCENARIO = '{{"name":"Scenario", "expression":"#Scenario", "sampleValue":"Actual"}}')
AND SALES.MEASURESID = (SELECT MEASURESID FROM MEASURES WHERE CHILD = '{{"name":"Measures", "expression":"#Measures", "sampleValue":"Sales"}}')
AND YEARDIM.MONTHNUM IN (SELECT MONTHNUM FROM YEARDIM WHERE MONTH IN {{"name":"Year", "expression":"#Year", "drillToBottom":"true", "sampleValue":"Qtr1"}})

In the Datasets Metadata Editor, select the 'New' button to create a new Dataset. In the new Dataset, set the ConnectorObjectTypeID property to DrillbridgeConnector. In the properties for the connector, fill in the ConnectionID property, the ReportAlias property, and other appropriate properties.

image

Next, complete the DrillthroughDimensions` property to define the dimension information to be passed to Drillbridge for use in Drillbridge tokens to filter the report. The Drillbridge connector can use either selectors or member mappings to pass information to Drillbridge. Below is an example of a DrillthroughDimension defined to use a Dodeca selector.

image

Below is an example of a DrillthroughDimension defined to use a member definition. Member values may be tokenized.

image

Commit the changes to the Dataset and you are ready to use it in a Universal Range.

Note: Testing of Drillbridge Datasets are not currently supported in the Dataset Editor.

Drillbridge Connector Universal Range Notes

The Universal Range DataTable Editor DataTableName requires a value, but this value is not used in the Drillbridge Connector. You can use any string to provide a value for the property, but the best practice is to use the data table name data as shown below.

image

It is common to use an Essbase selector in conjunction with a Universal Range that uses the Drillbridge Connector. However, the Universal View Type does not support a default Essbase connection. Thus, when using selectors with this connector, each selector will need to be mapped to a specified connection.

Below is an example of a view that uses a Drillbridge Dataset.

image