olap - Advice on creating analytical query (SQL) generator -

we migrating microsoft's analysis services (ssas) hp vertica database our olap solution. part of involves changing our query language mdx sql. had custom mdx query generator allowed others query data through api or user interface specifying needed dimensions , facts (outputs). generated mdx queries ok , didn't have handle joins manually.

however, using sql language , since keeping data in different fact tables need find way how generate queries using same dimension , fact primitives.

eg. if user wants see client name number of sales, might take request:

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

and generate query this:

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

and gets more complicated quickly.

i thinking graph based solution store relations between dimension , fact tables , build required joins finding shortest path between nodes in graph.

i appreciate information on subject including keywords should use searching solution type of problem or 3rd party products solve it. have tried searching it, problems quite different.

you can use free mondrian olap engine can execute queries written in mdx language on top of relational database (rdbms).

for reporting can try saiku or pentaho bi server on of mondrian olap.