I am doing research to see what is the best way to take data from a live database, and transform it to be able to put the data into data cubes. Currently as a prototype, we used normal queries, to copy data from the live database as a sequence of SQL Statements, and put into the intermediate DB that is used for OLAP, but for the real thing we are considering to use MDX.
Any tips, if this is the best and most efficient way?
I think you're making a mistake, you can not use MDX to load data from a db to an olap server. MDX is not an language for ETL, or I'm missing somehting.
As you're describing is a 'standard' way for doing this (actually do you need the intermediate db or a set of new tables, materialized views and views is enough)