Skip to main content

Documentation Index

Fetch the complete documentation index at: https://docs.activeviam.com/llms.txt

Use this file to discover all available pages before exploring further.

PowerBI itself does not support multidimensional datasources. It does not let end users explore cubes interactively, like they can with a tabular datasource supporting the DirectQuery feature. But on the other hand you can import data from an Atoti cube into PowerBI and use this data to create dashboards. A primitive method for doing that is to export the output of a query into a CSV file, and load this CSV file into PowerBI. It always works. If you only have access to Atoti Server without the Atoti Python library, that’s what you have to do. A more integrated way is to plug Atoti as an MDX source, returning an MDX result that PowerBI can display. Finally, you can use the Atoti Python library. Here we’re looking at an online technique that does not require to transfer files and that supports data refresh: we’ll be using the Atoti client Python library within the PowerBI Python connector.

​
Connecting to Atoti in PowerBI using an MDX query

Open Power BI, click on “Get Data” drop down menu from the ribbon, select the “Blank Query” data source type. This will open the Power Query editor.
In the Power Query editor, click on the “Advanced Editor” from the ribbon. We are going to describe the connection details and the MDX query to the Atoti+ cube using the Power Query M language.
In the advanced editor window, describe the cube connection and the MDX query. For this example we are connecting to the Atoti sandbox cube, and retrieve PnL metrics at the book level. Note that the MSOLAP client library must be installed on the computer for the Power Query script to work. Otherwise you’ll get an error telling you that it is not installed. You can get the MSOLAP client library from there: https://docs.microsoft.com/en-us/analysis-services/client-libraries 
let
  Source = OleDb.Query(
              "Provider=MSOLAP;
               Data Source=http://localhost:9090/xmla;
               Initial Catalog=Catalog",
              "SELECT {
                 [Measures].[contributors.COUNT],
                 [Measures].[pnl.SUM],
                 [Measures].[pnlDelta.SUM],
                 [Measures].[pnlVega.SUM]
               } on COLUMNS,
               [Booking].[Desk].[BookId].Members
               DIMENSION PROPERTIES MEMBER_CAPTION on ROWS
               FROM [EquityDerivativesCube]"
             )
in
  Source
Click on “Done” and Power Query will display a warning to remind you that you are running a native query on a database. You must approve this query by clicking on “Run”.
Then you will edit your Atoti+ credentials to connect to the Atoti+ cube. Choose “Database” in the left drawer and enter your Atoti+ user name and password. Click on “Connect”.
Power Query will connect to the Atoti+ cube, run the MDX query and present the result set to you. From that point you can use the Power Query features to rename, remove and reorder columns, or even filter some rows out and do other transformations. The MSOLAP provider returns fully qualified names for cube levels and measures, which you will probably want to rename into something more intelligible.
All the data manipulations in Power Query can be achieved through point and click, or with Power Query M code. For instance here is a refined Power Query M script that will also rename the columns automatically, and change the data type of numerical columns that are sometimes not detected properly by Power Query: 
let
  Source = OleDb.Query(
              "Provider=MSOLAP;
               Data Source=http://localhost:9090/xmla;
               Initial Catalog=Catalog",
              "SELECT {
                 [Measures].[contributors.COUNT],
                 [Measures].[pnl.SUM],
                 [Measures].[pnlDelta.SUM],
                 [Measures].[pnlVega.SUM]
               } on COLUMNS,
               NON EMPTY [Booking].[Desk].[BookId].Members
               DIMENSION PROPERTIES MEMBER_CAPTION on ROWS
               FROM [EquityDerivativesCube]"
             ),
  
  RenamedColumns = Table.RenameColumns(Source,
  {
    {"[Measures].[contributors.COUNT]", "Count"},
    {"[Measures].[pnl.SUM]", "Pnl"},
    {"[Measures].[pnlDelta.SUM]", "PnlDelta"},
    {"[Measures].[pnlVega.SUM]", "PnlVega"},
    {"[Booking].[Desk].[BookId].[MEMBER_CAPTION]", "BookId"},
    {"[Booking].[Desk].[LegalEntity].[MEMBER_CAPTION]", "LegalEntity"},
    {"[Booking].[Desk].[Desk].[MEMBER_CAPTION]", "Desk"},
    {"[Booking].[Desk].[BusinessUnit].[MEMBER_CAPTION]", "BusinessUnit"}
  }),
   
  ChangedTypes = Table.TransformColumnTypes(RenamedColumns,
  {
    {"Count", Int64.Type},
    {"Pnl", type number},
    {"PnlDelta", type number},
    {"PnlVega", type number}
  })
  
in
  ChangedTypes
This will return a cleaner and ready to use dataset. You can click on “Close & Apply” which will take you back to Power BI and you will be able to design a report with the imported data.

​
Connecting to Atoti in PowerBI using a Python script

Prerequisites are to have Python 3.8+ (64 bit) installed, to have PowerBI configured for running Python scripts, and to have the Atoti Python library installed. In case several Python environments are installed on the machine, then Atoti must of course be installed in the environment referenced in the PowerBI configuration. To create our sample Atoti cube let’s use the Atoti tutorial. After running the first few cells of the first tutorial notebook we have a cube called “Sales” with a few hierarchies and measures, we’re going to use this cube for our experiment. We need to record the port of this cube (that we get by evaluating session.port in the notebook) for later use. In PowerBI we open the Get Data wizard and select the Python script option.
Get Data Wizard
PowerBI opens a window where we can paste a simple Python script (expected to return a pandas dataframe). We’re going to do that with the Atoti Python library.
Python Script
Querying a remote cube with Atoti is covered in the tutorial. Here is an example: 
from atoti_query import QuerySession

session = QuerySession(
    'http://localhost:61172' # the url of the remote cube
)

cube = session.cubes["Sales"]
l = cube.levels
m = cube.measures
df = cube.query(
    m["contributors.COUNT"], m["Amount.SUM"], m["Quantity.SUM"],
    levels=[l["Date"], l["Shop"], l["Product"]]
).reset_index()
No security is used here. Look at the rest of the documentation for access to secured cubes, including cubes developed with Atoti and the Java API. Why the .reset_index() command applied to the Atoti query result? When processing cube queries, Atoti returns a dataframe with a hierarchical index that represents the hierarchies involved in the query. PowerBI does not support this pandas feature, and we must reset the index to a standard flat column. After you press ‘OK’, PowerBI will take a while to run the python script, get the data and make it available in the model for dashboarding as usual.
PowerBI Dashboard