xlbd-img

Online Product Manual

The Online Manual is the heart and soul of the site.

Here our aim is to make XLDB as easy to understand as possible. 

It is important to understand that  configuring XLDB requires some basic level of SQL programming; however provided that you have these skills or have access to someone who does (and we can arrange this for you too) then using the online guide, perhaps with some support from our YouTube page, Blog and Forum should be all that you need use XLDB.

Your experience is important to us. If there is anything unclear in the manual or indeed any part of the website, please let us know and we will do our best to help and in most cases will be more than happy to update our content

O

Parent / Child Ragged Hierarchies

Background: Parent / child hierarchies are an important part of accounting, it is very rare that you would ever find an ‘even and balanced’ Account dimension hierarchy for instance. For this reason we went to great lengths to accommodate parent child hierarchies in XLDB.

I will not be delving into exactly how to create Parent Child hierarchies for Power Pivot, but there are some excellent resources online, here are a couple:

https://docs.microsoft.com/en-us/dax/understanding-functions-for-parent-child-hierarchies-in-dax

And (highly recommended)
https://www.daxpatterns.com/parent-child-hierarchies/

One of the biggest challenges for parent child hierarchies in XLDB is the referential integrity. As mentioned earlier in the manual, XLDB relies on strict referential integrity and star schema structures based on leaf level elements .

However using the parent child methods referenced above, it is necessary to reference a dimension that contains a child and parent structure with a mixture of leaf and consolidated elements in the one field.

In order to accommodate this, XLDB has been designed to be able to substitute the Parent / Child for the regular dimension.

Lets take a look at how this is accomplished in steps:

  1. Build your cube using the standard star schema referred to in this manual.
  2. In the database layer, create your Parent Child dimension (separate from the star schema) in accordance with the methods outlined in the examples referenced above; based on 2 field: Child and Parent. *Important*: Name your parent / child dimension they same thing as the underlying dimension except with the suffix “_PC” For example, in the star schema, call your dimension “Dim_Accounts” and your equivalent parent child dimension “Dim_Accounts_PC”

  3. In the PowerPivot layer, link your Fact Table to the Parent / Child dimension (see the referenced examples again for how to do this) instead of the regular dimension i.e. Dim_Accounts_PC instead of Dim_Accounts
  4. Naming your hierarchical levels is an important consideration. You must name your levels in the parent child dimension with the same name as the field that is being referenced in the standard dimension (being the dimension referenced in the Star Schema).
  5. Per the referenced examples you will need to create a new dimension measure (as distinct from a regular member) to use the parent child hierarchy. For XLDB, name this measure the same as the Fact table (cube) measure, but with the UPPERCASE suffix of your choice. *Remember that Measure field names must be unique. For Example, if your cube measure is called gp1_Amount, name your Dimension measure gp1_Amount_APC

Suggest Edit