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.

Multi-level hierarchy

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"]
}

Types

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

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.

Single Sign On

You can use SSO to connect XLCubed to Incorta from Version 2309.1. To do this an admin must configure the SSO setup in XLCubedWeb, and then the user configures the web sso url where this is stored.

XLCubed Web

Edit the web.config to add the following at the top of the file, replacing the relevant attributes with the values from your SSO provider.

<?xml version="1.0" encoding="UTF-8"?>
<configuration>  
 <configSections>
  <section name="incorta" type="XLCubed.Data.Incorta.Configuration.IncortaConfiguration, XLCubed.Data.SQL"/>
 </configSections>
 <incorta>    
  <oauthApps>
   <add incortaUri="https://myserver.mydomain.com/incorta"
    tenant="tenantName"
    clientID="client_id"
    clientSecret="client_secret"
    authorizeUri="https://sso-provider.com/authorize"
    tokenUri="https://sso-provider.com/oauth/token"
    redirectUri="https://www.xlcubed.com/SSOToken.html"
   />
  </oauthApps>  
 </incorta>
 <appSettings>

The in XLCubed Excel set up the web url

The address will be similar to this, but with your own server name http://localhost/XLCubedWeb/WebServices/RepositoryService.svc/IncortaOAuthApps

Note: If you are not using Windows authentication then you should copy the contents of the IncortaOAuthApps page to an unauthenticated location and use that in Excel as the OAuth Info Url.

Then in the connection screen select the SSO authentication type and enter your server/tenant details as normal. When you press connect you will be prompted to log on.