The request we hear most often about Essbase data in Power BI is, “Is it possible to display Essbase data using Essbase hierarchies and calculations in Power BI?” The answer is, “only with ExoInsight”. The screenshot below shows a P&L with members in Essbase outline order and amounts at upper levels also coming from Essbase:

As you can see, there’s no need to export data from Essbase (which gets quickly get out of sync) or to create and maintain separate tables to store your outline export in Power BI. ExoInsight does everything for you automatically.

Only ExoInsight provides the 3 things necessary to recreate this P&L in Power BI:

1. A direct connection to Essbase to pull your data in real-time.
2. The metadata from your Essbase outline, broken out into a separate column for each generation.
3. A Member Order column for each dimension, or descendants within a dimension (you don’t have to pull the whole dimension if you don’t need it for the given report), that gives a sequential number to each member representing its order in the Essbase outline.

There are some steps you must take in Power BI to prevent its default behavior of aggregating data:

1. In addition to data, ExoInsight also retrieves metadata (your cube’s dimensionality) for the dimensions you specify. The ExoInsight Power BI template provides an example of pulling a single dimension. You can easily right click on the “dimension” query and select “Duplicate” to pull in metadata from multiple dimensions:

2. The ExoInsight template automatically creates the proper joins for your dimension to the GetData_POV table, allowing you to focus on creating your report rather than technical details:

3. ExoInsight creates a Member Order column in each “dimension” query. This column represents the order of each member as it appears in the Essbase outline. The Member Order column must be included in each visualization where you want to preserve the Essbase member order. For it to work correctly, you must change the data type from alphabetical to number, as shown:

4. Back in the visualization:

• Click the Amt field in GetData_POV

• Go to the Modeling tab of the Power BI desktop ribbon and change the Default Summarization to “Don’t summarize”. This prevents Power BI from summarizing data that Essbase has already correctly aggregated.

5. Recreate the Essbase hierarchies in Power BI:

• Right click on the “Measures GEN_1” field on the “dimension” query (or the name you used if you renamed it, which is recommended for clarity) :

• Drag and drop the other “GEN_” fields and the Member Order field on top of the new hierarchy to create the following result:

6. After adding the matrix visual and putting Amt (after changing the Default Summarization to “Don’t summarize” – as illustrated in point 4 above), you’ll need to make the following matrix formatting changes:

• Turn off Stepped Layout for both “Column headers” and “Row headers”:

• Turn off Row subtotals:

You should now have a matrix visualization that looks as similar to a Smart View retrieve as is currently possible in Power BI, while also retaining Essbase member order and aggregations. This means that totals for your upper-level members are coming directly from Essbase, so they take into account the proper signs (+, -, ^, ~) and calculations.