How to fetch data from external datacubes for a 'data reservoir' in SQL in local machine

See more:

SQL

MDX

Hi Guys,

Greetings.

I need to create a 'data reservoir' (a database) in SQL in a local machine (will act like a local server and will be accessible to a few analytics-team members), where a limited amount of data will be stored. This 'data reservoir' should be connected to multiple external OLAP DataCubes. We need to fetch data as per our requirement (already coded in a SQL script) from those Cubes on a weekly basis.

It's for the first time I am trying this and I have no clue. Hence, here are my questions-

1. What is the entire process flow of this? (How do I connect, How do I query in cube, How do I bring that data to my local 'data reservoir'?)

2. Do I need to use MDX queries? If yes, then what guideline shall I follow as MDX is also a first-time for me.

3. I feel comfortable in SQL. Can I use SQL as much as possible in this entire process flow? I am using MicrosoftSQLServer.


Thanks in advance :)

What I have tried:

I have accumulated all required fields till lowest granularity of items in a single SQL query. This is our data that is required for analysis, approx 1 - 1.2 GB of size.