CODEWITHSUNDEEP
sqlolapbuilderverticarolap
Charlie Brad.
Charlie Brad.

Reputation: 1

Advice on creating analytical query (SQL) generator

We are migrating from Microsoft's Analysis Services (SSAS) to HP Vertica database as our OLAP solution. Part of that involves changing our query language from MDX to SQL. We had a custom MDX query generator which allowed others to query for data through api or user interface by specifying needed dimensions and facts (outputs). Generated MDX queries were ok and we didn't have to handle joins manually.

However, now we are using SQL language and since we are keeping data in different fact tables we need to find a way how to generate those queries using same dimension and fact primitives.

Eg. if a user wants to see a client name together with a number of sales, we might take a request:

dimensions: { 'client_name' }
facts: { 'total_number_of_sales' }

and generate a query like this:

select clients.name, sum(sales.total)
from clients
join sales on clients.id = sales.client_id
group by 1

And it gets more complicated really quickly.

I am thinking about graph based solution which would store the relations between dimension and fact tables and I could build the required joins by finding shortest path between the nodes in a graph.

I would really appreciate any information on this subject including any keywords i should use searching for a solution to this type of problem or 3rd party products which could solve it. I have tried searching for it, but the problems were quite different.

Upvotes: 0

Views: 419

Answers (1)

mzy
mzy

Reputation: 1764

You can use free Mondrian OLAP engine which can execute queries written in the MDX language on the top of relational database (RDBMS).

For a reporting you can try Saiku or Pentaho BI server on the of Mondrian OLAP.

Upvotes: 1

Related Questions