XLCubed is now FluenceXL. The new wiki can be found here: https://help.fluencexl.com/

Difference between revisions of "Incorta"
(→‎Single Sign On)
 
(9 intermediate revisions by the same user not shown)
Line 13: Line 13:
 
===Definition===
 
===Definition===
  
−
The hierarchy definitions must be held in a schema called {{code|XLCubedMetadata}}. This should contain a table called {{code|Hierarchies}} and has three columns {{code|Schema}}, {{code|Table}} and {{code|Json}}.
+
The hierarchy definitions must be held in a schema called {{code|XLCubedMetadata}}. This should contain a table called {{code|Hierarchies}} which has three columns {{code|Schema}}, {{code|Table}} and {{code|Json}}.
  
 
The Schema and Table defines where the hierarchy will appear in the XLCubed UI, and the Json defines the hierarchy structure.
 
The Schema and Table defines where the hierarchy will appear in the XLCubed UI, and the Json defines the hierarchy structure.
Line 19: Line 19:
 
There are two types of hierarchy, multi-level and self-join.
 
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.
 
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.
Line 25: Line 25:
 
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.
 
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.
  
−
<code>{
+
{
 
     "name": "PROD_HIER",
 
     "name": "PROD_HIER",
 
     "label": "Products",
 
     "label": "Products",
Line 43: Line 43:
 
         }
 
         }
 
     ]
 
     ]
−
}</code>
+
}
  
 
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.
 
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.
Line 49: Line 49:
 
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, ...)
 
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, ...)
  
−
<code>{
+
{
 
     "name": "ORDERDATE_YQM",
 
     "name": "ORDERDATE_YQM",
 
     "label": "Order Date YQM",
 
     "label": "Order Date YQM",
Line 72: Line 72:
 
         }
 
         }
 
     ]
 
     ]
−
}</code>
+
}
 +
 
  
 
====Self-join hierarchy====
 
====Self-join hierarchy====
Line 78: Line 79:
 
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.
 
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.
  
−
<code>{
+
{
 
     "selfJoinFields": ["EMAIL", "JOB_TITLE"]
 
     "selfJoinFields": ["EMAIL", "JOB_TITLE"]
−
}</code>
+
}
 +
 
 +
 
 +
===Types===
 +
 
 +
In the date example there are {{code|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===
 
===Example===
Line 86: Line 92:
 
This Excel workbook contains sample hierarchies that you can use as a template for your own upload.
 
This Excel workbook contains sample hierarchies that you can use as a template for your own upload.
  
−
[[Media:Example.jpg]]
+
[[Media:IncortaHierarchies.zip]]
  
 
===Scaling considerations===
 
===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
 +
[[File:IncortaSSOExcelConfig.PNG|center|600px]]
 +
 +
The address will be similar to this, but with your own server name
 +
{{code|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.

Latest revision as of 14:17, 20 September 2023

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

IncortaSSOExcelConfig.PNG

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.

Retrieved from ‘https://help.xlcubed.com/index.php?title=Incorta&oldid=11194’