Skip to main content

Querying Atoti Server: Third Party Front Ends

Microsoft Excel

Microsoft Excel communicates seamlessly with Atoti Server by way of the XMLA protocol, without the need to install any add-ons.

Excel on Atoti supports the same set of functionalities as Excel on Microsoft SQL Server Analysis Services 2008.

For each new Atoti Server release, we test queries on the sandbox project from Microsoft Excel, thus making sure we stay compatible.

Connecting to an Atoti Cube in Excel via the MSOLAP Data Provider

The MSOLAP data provider is also referenced in other articles as the MSAS driver since it is designed to connect to a Microsoft SQL Server Analysis Services server.

Here are the steps to configure a connection using the MSOLAP 10.0 data provider, including steps related to security configuration. The screen captures are made with Excel 2010. First, open the data tab in Excel and choose to connect to a MSAS server:

Data from Analysis Services

Then enter the connection information and hit next: Connection Wizard In this example, http://localhost:9090 is the host and port where Atoti Server is running (defaults of the Atoti Server sandbox project) and /xmla is the path to the XMLA service on Atoti Server, with which Excel will communicate.

You are then prompted to choose the catalog (called database in this window), here we choose Sandbox, and then the cube: Choose Catalog and Cube

Excel is now asking where to place the pivot table, and here we can change the properties of the connection when choosing the Properties... button: Accessing Connection Properties

Here we ask Excel to remember the password in the connection file it will create. Of course this is an optional step, you may want to be asked the password at every connection for obvious security reasons. The sandbox project has hardcoded credentials and does not hold sensitive data, so we go for the easiest route. Often on a "real" project, SSO will be enabled, and thus you can reuse your windows credentials.

Other parameters of the connections can be changed in this window too: Properties: Save Connection Password

Then Excel asks for the password again: Save Connection: Enter Password

After having entered the password that will be saved in the connection file, we can again choose the catalog we want to connect to, we hit Finish. Save Connection: Choose Catalog

That's done! An empty pivot table appears on Excel, and you can choose the fields to add to it: Excel with Empty Pivot Table

Tableau

Tableau is probably one of the most well-known front-ends for business analytics. Several of our customers use it as a front-end for Atoti Server.

Connecting to an Atoti Cube from Tableau

The screenshots are from Tableau 2018.2. Start Tableau and click on Microsoft Analysis Services. Tableau Choose Microsoft Analysis Services

If you are unable to see Microsoft Analysis Services in the list, please install drivers for 2014 SP1 (version 12.0.2000.8) from Tableau's Driver Download page.

Enter the Atoti Server address (do not forget the /xmla that gives access to the XMLA servlet which simulates a Microsoft Analysis Services answering Tableau's requests), and your username/password as configured in Atoti. Tableau Enter Server Details

Once you are able to connect, you will see the Cubes as below: Tableau Choose Cube

Select the Cube you are interested in, it will then show the dimensions and measures: Tableau Choose Cube

Once you have selected dimensions and measures to display, you have your pivot table! Tableau Choose Cube

You can now create your charts and reports with Tableau using data from Atoti.

How to Convert a Field from String to Date

It is a known issue that sometimes Date fields are shown as Strings in Tableau.

  1. Change the data type of the field, changing it from String to Date
  2. If your dates are not recognized (and shown as null in the data view), revert the data type to String and create a calculated member. A calculated member is an MDX expression that will be evaluated during query.
    From the Dimensions panel, select Calculated Members and create a new one. Choose a name, make sure to select Date as its result type and write the MDX expression using the `cdate` function. As an example, the MDX formula to use to transform the date field "AsOfDate" on the "HistoricalDates" from the "Time" dimension is ```cdate([Time].[HistoricalDates].CurrentMember.MemberValue)```.

Microsoft PowerBI

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

MDX query for PowerBI (click to expand)
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:

MDX query with improved display (click to expand)
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

Other front-ends

How can I know if my front-end is compatible with Atoti Server?

In order to be compatible with Microsoft Excel, we have developed an XMLA service that mimics the behavior of Microsoft SQL Server Analysis Services. All front-ends that use the XMLA protocol should be compatible with Atoti Server.

Do you guaranty compatibility with all front-ends using the XMLA protocol?

No, we do not monitor every front-end to make sure we maintain compatibility. Some front-ends may break compatibility in newer versions.

Do you guaranty performance with all front-ends using the XMLA protocol?

The performance of the association of Atoti with a specific front-end will be largely dependent of the MDX queries generated by the front-end. That is a variable that we do not control, so we cannot guaranty the resulting performance.

Do you offer support for other front-ends?

Yes, if you want to use a specific XMLA-enabled front-end, we encourage you to just try it and report any difficulty. We will do our best to help you.