Incorta

In Version 10 XLCubed 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 will be detected, otherwise you'll also need to enter that information.

e.g. http://myServer/incorta/!tenantName/

You can then authenticate log in and access the data model.

Hierarchies

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

Definition

The hierarchy definitions must be held in a schema called XLCubedMetadata. 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 XLCubed 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 is 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 XLCubed calls the hierarchy and levels and must not 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 formulae to define the levels of the hierarchy. In this case we have a date field and want to create a multi-level hierarchy from that without adding extra calculations to our model.

We have simple a simple formula to define the content of each level, and for some levels add a sortFormula to control the order. (This prevents the months being ordered April, August, ...)

{
   "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-join hierarchy

Self-joins can be defined in Incorta and used in XLCubed. In this case we defined the fields in the self-joined table that we want to use as not all will be relevant. e.g. in an Employees table the FullName would make a good hierarchy, but Title would not.

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

Example

This Excel workbook contains sample hierarchies that you can use as a template for your own upload.

Media:IncortaHierarchies.zip

Scaling considerations

When you define a hierarchy XLCubed will download the whole hierarchy when it is first used. This means that the maximum size of the hierarchy is limited by system memory and network capacity. Query performance will also be slower if the hierarchies are very large.

For this reason you should not 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 contain a very large number of items so it would make sense to have the lowest level in the hierarchy at a higher level of the Product table.

Date tables sometimes contain more rows than are realistic, for example they do not need to contain many decades of dates for which there is no actual data in the joined tables.