Connect to Incorta data sources in Anaplan XL

Anaplan XL supports connections to Incorta data sources.

In the connections screen, select the Incorta type and enter the URL you would normally use to access the Incorta front end as the server name. If this includes the tenant name, then that'll be detected, otherwise you'll also need to enter that information.

You can then authenticate, login, and access the data model.

You can add custom metadata to the Incorta instance that defines hierarchies which will be visible in Anaplan XL. These enable drilldown and custom calculations to be added.

The hierarchy definitions must be held in a schema called AnaplanXLMetadata. This should contain a table called Hierarchies, which has three columns, Schema, Table, and Json.

The Schema and Table defines where the hierarchy will appear in the Anaplan XL UI, and the Json defines the hierarchy structure.

There are two types of hierarchy, multi-level and self-join.

This example shows a product hierarchy with three levels, each ‌based on a column in the table. This works well if your levels are already defined in the existing schema.

The name attributes define what Anaplan XL calls the hierarchy and levels and mustn't be changed once the hierarchy is in use in reports. The labels can be freely changed.

{ "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 shows using formulas to define the levels of the hierarchy. In this case, you have a date field and want to create a multi-level hierarchy from that without adding extra calculations to your model.

You have a simple formula to define the content of 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" } ] }

Self-joins can be defined in Incorta and used in Anaplan XL. In this case, you can define the fields in the self-joined table that you want to use as not all will be relevant. For example in an Employees table, the FullName would make a good hierarchy, but Title wouldn't.

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

In the date example, there are type attributes for the hierarchy and levels. These help Anaplan XL know the meaning of the hierarchy and are useful in some custom calculations. For example, if the year level has the proper type, the the YTD () MDX function can be used.

When you define a hierarchy, Anaplan XL will download the whole hierarchy when it's first used. This means that system memory and network capacity limit the maximum size of the hierarchy. Query performance will also be slower if the hierarchies are very large.

For this reason, you shouldn't make hierarchies that are very large (more than 10,000 items is a good guide) without testing their impact.

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. It'd be best to put the lowest level in the hierarchy at a higher level of the Product table.

Date tables sometimes have more rows than are real. For example, they don't need to have many decades of dates for which there's no real data in the joined tables.