Is using Views with SQL Join Queries in OLAP Cube a good practice?

Question

I'm trying to create my first olap cube schema for Mondrian server. I came up with creating a view as a fact table with several joins to put foreign keys into it, let's say it looks like this:

SELECT call.id, record.user, topic.id, session.id, user.id, session.end, session.start FROM calls call
  LEFT JOIN call_records record ON (record.call_id = call.id)
  LEFT JOIN topis topic ON (topic.id = record.topic_id)
  LEFT JOIN login_sessions session ON (record.session_id = session.id)
  LEFT JOIN users user ON (session.user_id = user.id)

I also created a Dimension called Users, Topics (and some more but I'm trying to keep it simple) and everything's working fine and I retrieved all the information I needed except for the summary of working time on the Topic or for each user.

I can't just sum session.end - session.start because session table is joined and the same session occurs multiple times.

Plus I don't think I'm doing it the way I'm supposed to.

Can you help me do it the right way? Thanks

Luc Luc · Accepted Answer · 2013-08-12T14:57:09

Creating an OLAP model over a normalized dataset is usually bad practice. You should read about denormalized models for OLAP systems and how ETL / ELT techniques are leveraged to create these.

There is a lot of literature out there to help you out. I'd suggest getting familiar with OLAP 'star' schemas and ETL tools.

Is using Views with SQL Join Queries in OLAP Cube a good practice?

1 Answers