Connect to Incorta data sources in Anaplan XL

Anaplan XL Reporting supports connections to Incorta data sources. 

You can connect Anaplan XL to Incorta data sources to define hierarchies that enable drilldown and custom calculations. 

In the Manage Connections window, select Incorta from the Dimensional tab in Connections. Enter the URL you normally use to access the Incorta front end as the server name. If the URL includes the tenant name, the system detects it automatically. Otherwise, you also need to enter the tenant name.

Then you can authenticate, log in, and access the data model.

You can add custom metadata to the Incorta instance to define hierarchies that Anaplan XL displays. These hierarchies enable drilldown and custom calculations.

Hold the hierarchy definitions in a schema called AnaplanXLMetadata. This schema should contain a table called Hierarchies with three columns: Schema, Table, and Json.

The Schema and Table columns define where the hierarchy appears in the Anaplan XL user interface, and the Json column defines the hierarchy structure.

Two types of hierarchy exist: multi-level and self-join.

This example shows a three-level product hierarchy, each based on a table column. This approach works well if your levels already exist in the current schema.

The name attributes define what Anaplan XL calls the hierarchy and levels. Don't change these names once the hierarchy is in use in reports. You can change the labels.

{ "name": "PROD_HIER", "label": "Products", "isDefault": true, "levels": [{ "name": "PROD_HIER_CAT", "label": "Product Category", "field": "Online_Store.OrderDetails.CategoryName" }, { "name": "PROD_HIER_SUBCAT", "label": "Product Subcategory", "field": "Online_Store.OrderDetails.SubcategoryName" }, { "name": "PROD_HIER_PROD", "label": "Product Name", "field": "Online_Store.OrderDetails.ProductName" } ] }

This example uses formulas to define hierarchy levels. In this case, you have a date field and want to create a multi-level hierarchy from it without adding extra calculations to your model.

You can use a simple formula to define each level, and for some levels, add a sortFormula to control the order.

{ "name": "ORDERDATE_YQM", "label": "Order Date YQM", "type": "time", "levels": [{ "name": "DATE_Y", "label": "Year", "formula": "Year(Online_Store.OrderDetails.OrderDate)", "type": "timeyears" }, { "name": "DATE_YQ", "label": "Quarter", "formula": "\"Q\" + quarter(Online_Store.OrderDetails.OrderDate) + \" \" + year(Online_Store.OrderDetails.OrderDate)", "sortFormula": "(year(Online_Store.OrderDetails.OrderDate) * 100) + quarter(Online_Store.OrderDetails.OrderDate)", "type": "timequarters" }, { "name": "DATE_YQM", "label": "Month", "formula": "monthName(Online_Store.OrderDetails.OrderDate) + \" \" + year(Online_Store.OrderDetails.OrderDate)", "sortFormula": "(year(Online_Store.OrderDetails.OrderDate) * 10000) + (quarter(Online_Store.OrderDetails.OrderDate) * 100) + month(Online_Store.OrderDetails.OrderDate)", "type": "timemonths" } ] }

You can define self-joins in Incorta and use them in Anaplan XL. In this case, you define the fields in the self-joined table that you want to use, since not all fields are relevant. For example, in an Employees table, the FullName field makes a good hierarchy, but the JOB_TITLE field doesn't.

{ "selfJoinFields": ["EMAIL", "JOB_TITLE"] }

In the date example, the hierarchy and levels include type attributes. These attributes help Anaplan XL understand the hierarchy’s meaning and can be used to perform custom calculations. For example, if the year level has the correct type attribute, you can use the YTD() MDX function.

When you define a hierarchy, Anaplan XL downloads the whole hierarchy the first time you use it. This means system memory and network capacity limit the hierarchy’s maximum size. Large hierarchies also slow down query performance.

For this reason, don't create very large hierarchies (more than 10,000 items is a good guide) without testing their impact first.

You can limit the hierarchy size by not adding a level for every column in your table. For example, a SKU level could have many items. Move the lowest hierarchy level up in the Product table.

Date tables sometimes have more rows than you need. For example, they don't need decades of dates where the joined tables have no real data.