CODEWITHSUNDEEP
postgresqlmdxolapbusiness-intelligenceolap-cube
user3597931
user3597931

Reputation:

How can I run MDX queries against a PostgreSQL database?

If I have a PostgreSQL server running with my data already structured in facts and dimensions, how can I run MDX queries against it?

Let's suppose each row in the fact table is a sale, so the fact table has the following columns: id, product_id, country_id and amount.

And the dimension tables are very simple: product_id and product_name, and country_id and country_name.

How should I proceed to be able to run MDX queries against this data? I tried downloading Mondrian but I found it very hard to use.

Please keep in mind I am not a developer, so my technical skills are limited; I work at an investment fund and I want to be able to run more powerful analysis on our data sets. But I do have some basic knowledge on SQL and I can code a little bit in Ruby.

Upvotes: 1

Views: 6393

Answers (2)

Stavros Koureas
Stavros Koureas

Reputation: 1482

Still today year 2023 PostgreSQL is doesn't have anything like Microsoft Analysis Services (Tabular or Multidimensional) which can pre-calculate data on different dimension levels to speedup the query time.

Currently the solutions are to use 3dparty tools which are processing your DWH and making an OLAP database. In this case you will not be able to use fancy popular front ends like PowerBI as the connector probably will be missing, or even the software provides the connector it will be only usable from PowerBI Desktop and not the PowerBI Service. This makes the roll-outs and maintenance even harder when it comes for production architectures.

My experience is not so good while having PostgreSQL with millions of data into tables and using PowerBI direct query, so defiantly this would be the best feature if PostgreSQL ever built such a thing natively.

Upvotes: 0

mzy
mzy

Reputation: 1764

As you already have a DWH (data warehouse) in PostgreSQL which contains dimension tables and fact tables, now you are two steps from building simple analysis solution. The solution I recommend consists of:

Steps:

  1. Download the OLAP schema workbench tool. Using this tool you can create Mondrian OLAP schema easily on the top of the existing tables (dimensions, facts) of your DWH.
  2. Once you create the OLAP schema, download the Saiku Analysis application, configure it to use your OLAP schema and your DWH
  3. Run Saiku - you can run MDX queries on the DWH or do ad-hoc data analysis by drag&drop of measures (amount, etc.) and dimensions (product name, country name).

Upvotes: 8

Related Questions