xlbd-img

Glossary of terms / Data dictionary

Coming soon

Case studies and templates to download

Coming Soon

How to use XLDB

Coming soon

String Measures

We can display text (string) measure values in PowerPivot and we can write these string values back to the database using XLDB

To do this you will need to do the following:

  1. In SQL Server, create your table string measure. This can be named anything that you want, but like all measure names they must be unique.
     
    1. Right click on your cube name and select Add Measure


    2. Enter the formula pre the format example:
      =if(HASONEVALUE(Fact_AccountPlanMapping[AcctMapType]), Values(Fact_AccountPlanMapping[AcctMapType]),Blank())
    3. Important** for XLDB to read the string values, you must name the new measure the same name as the table measure with the prefix of “s” so in the above case, the field name is AccMapType and the new PowerPivot measure is sAccMapTyp

 

Parent / Child 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

Design Requirements for XLDB

This section deals with all aspects of design for XLDB, including SQL Server, PowerPivot modelling and DAX

SQL Server naming conventions for XLDB

Coming soon

SQL Server design needed for XLDB

Coming soon

How to connect XLBD to your SQL database

Coming soon