Creating a hierarchy for a data cube

#1

Hi,


I'm keen to configure my "Process" area of the cube so that it understands that Board level > Site is a hiearchy and are not single entities.


However every time I try it tells me that the hierarchy cannot be based on the cube I'm building? Seems a bit counterintuitive? Any advice would be very welcome.


Best,


J

#2

Any Time I make hierarchy I go to the global project and make a data cube that only has the the hierarchy structure I am wanting (no measures or other data. Then I make the hierarchy, still in the global project.


I can then apply that global hierarchy to any cube in any project.


An other tip from a mistake I made, make sure the colums have the name you are going to use every time other wise this cuases issue trying to match it something else, (it is the unique name that matters the most form what I understand).

#3

Hi,


I followed your suggested steps, creating a cube with only the details I need for the hierarchy before creating the hierarchy.


However when trying to apply it to my cube I recieved this error message: The hierarchy '[My Project] User Hierarchies Root Folder/Org to Site' cannot be used in the data cube because it is based on it.


When I tried applying it as part of the metric set I recieved this error message: The intended operation is valid only for auto-generated entities.


Any light anyone can share on this would be great.


Best,


Jack

#4

did you try to apply it to the cube that made the hierarchy?
you have to have a cube that has the data that make the hierarchy that never gets the hierarchy applied to it.


Not sure what you are doing when you say "applying it as part of the metric".


Layout


In Global Project:

Data Cube -- MyHierarcyData (this one makes the Hierarcy and does not need nor can it have the hierarchy applied to it)

Hierarcy -- MyHierarcy


In new Sales Project

Data Cube -- SalesData


From the Sales Project open the "SaleData" Data cube and apply the MyHierarchy to the apporiate column.


Then use the "SalesData" data cube in a metric set.



I broke it down because I am not sure (because I am not there seeing the setps that cause the error) what causeing the errors.


#5

It sounds like the hierarchy you created is using the same data cube you are trying to use with the hierarchy. As mentioned in the message - the hierarchy cannot be based on the same data cube. Instead try the following:

  1. Create a new data cube (it can be a copy of your current data cube).
  2. Configure the process results in the new data cube to use the hierarchy you already created.



That said, it is a best practice to try and create your hierarchies from data cubes or databases structures (i.e. tables/views) that only contains the possible values for your hierarchy rather than all of your data (in database terms, that would be: try to create your hierarchy from a dimension table and not your fact table). For example: if you have the following table:


ContinentCountrySalesYear

Europe France $2M 2017

Europe France $2.5M 2018

Europe Spain $1.5M 2017

Europe Spain $3.5M 2018



it will be better to create a data cube that only contains the distinct (aggregated) values of the continents and countries and then create your hierarchy based on that. So the data cube that will power the hierarchy will look like that:


ContinentCountry

Europe France

Europe Spain



To easily get this structure, simply configure your data cube as follow:

on your first transform only keep the Continent and Country fields

use the aggregate transform to get the distinct combination of values for those fields

build your hierarchy using this data cube

use the hierarchy in a data cube that contains all the fields you need including the measures (i.e. sales amount).



I hope this makes sense in terms