SQL Server Performance Forum – Threads Archive

Pump Aggregated Data from Cube to OLTP database

Hi, Very brief description of our architecture. Our OLTP production database is having line items which is to be aggregated and shown to High level users. At the same time very few users are required to drill down through this cube data, mostly all users are interested only in single aggregated value. So our main application is only interacting with OLTP Production database. It will not be a data consumer of the Cube. In our OLTP design we have few tables to store these aggregated data from Cube. Application will just pickup data from these table and show. So my question is how to get the aggregated data from Cube to these OLTP Tables. Do we need to write MDX query for this ?. Can we write a MDX query to pickup this aggregated value and insert into my OLTP table?(like Select * into kind of stuff) . I am new to MDX query, any suggestion would be highly appreciated Thanks
Madhu

---

Hi Madhu,
Can you explain why you need to store the aggregated data back into OLTP.
Your Front end application can even call OLAP db directly.
With 2005 MS has also enhanced the ADOMD object model.
Client application can connect to OLAP using ADOMD and can fire MDX queries to get data in resultset or in XML format. If you want to store OLAP data back in SQL server then,
I feel you can go for SSIS package where you can have .net script task
and from script fire MDX queries get the result in XML or in cellset format using ADOMD, and then load the result into SQL server.

---

Hi Ranjit, Why should store aggregated data in OLTP ? i was also asked the same question. but it make sense when i saw the requirement. I will explain … It is a web application in pharma domain. it basically deals with research data on medicines. There is a form which provides cost negotiation facility for conducting a medicinal test/study on a country/patient/hospital etc. In a single grid which is filled with Medicine and many other data from OLTP, should be able show related aggregated value in few columns of same kind of test. Basically what the application will show is , to conduct a BP test in India costs Rs. 100 on an average. This 100 figure is came from Cube for over a period of time/country/hospital/city etc. it is an aggregated value. Like this there are many requirements where the user want to see the aggregated value in same form , probably same grid. So i don’t know is it possible / desirable to interact with both OLTP and OLAP database from same application/form. I am sure, i am not able to depict the actual scenario, but it is very close to it. Pse , suggest if u have any other opinion Thanks…… thanks again Madhu

---

In this maqin application, what user needs is just aggregated value. they don’t need to drill down. The cube is used by very high level users to drill down and taking reports of various kind. So anyhow they need cube, otherwise we could have just aggregated the value in OLTP itself. Madhu

---

Madhu,
In my last post at the end I have also depicted my opinion on bringing data back into OLTP from OLAP.
Please have a look at it as well.

---

i saw that. but just want to clarify is it possible /Advisable to interact with OLTP and OLAP from same application and for that matter form. If possible also , will u recommend that (just for info). thanks Ranjit, i am troubling u a lot…. Madhu

---

<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by madhuottapalam</i><br /><br />i saw that. but just want to clarify is it possible /Advisable to interact with OLTP and OLAP from same application and for that matter form. If possible also , will u recommend that (just for info). <br /><br />thanks Ranjit, i am troubling u a lot….<br /><br />Madhu<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Madhu,<br />There is a slight confusion i guess.<br /><br />I had posted this before,<br />"If you want to store OLAP data back in SQL server then, <br />I feel you can go for SSIS package where you can have .net script task <br />and from script fire MDX queries get the result in XML or in cellset format using ADOMD, and then load the result into SQL server."<br /><br />this SSIS package, you can schedule to run every night after processing of your cube.<br />Package will be storing aggregated data from cube into OLTP and your application will be accessing SQL table right?<br />Sot above i have shown you the option of storing cube data back into SQL server.<br /><br />I would also like to mention that the application which we have makes connection with OLAP and OLTP both. So depending on your situation you need to decide.<br />I don’t see any harm in client accessing both OLAP & OLTP.<br /><br />I’m glad i could help you.[<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]<br />

---

Hi Ranjit, I am stuck in a similiar situation as Madhu’s. I need to get aggregated data from my cube into a SQL server database. I have read ur post, but im still lost, as i have no clue of how to go about creating an "SSIS package where you can have .net script task
and from script fire MDX queries get the result in XML or in cellset format using ADOMD, and then load the result into SQL server." Could you please give me some details of how to go about doing this. PLz, ur help will be much appreciated. I am totally new to analysis services and have no idea about SSIS at all.
Madhu, if u were able to solve ur problem , could u too give me some tips…. Thanks in advance!!

---

Hi ritika, I was designing our OLAP application architecture when i posted this. I have submitted the recommendations and the final conclusion what we arrived was we need to have .net application which will interact with OLAP and OLTP simultaneously and this part is still pending. Madhu

---

Hi,
Firstly you need to learn about creating SSIS packages, adding .net script task in a package, accessing analysis server using ADOMD and same way accessing SQL Server using ado.net.
For now consider the approach as below,
1>Create application which will connect to sql server as well as Analysis server.
This is possible in windows environment using .net as you get wide aspect of .net tools.
Instead of creating separate windows application, you can add .net script task in SSIS and then you can even scedule the package.
2>In .net make connection with sql server as well as AS server db.
Fire MDX query on AS cube using ADOMD object, get the result in XML or cellset format,
Read the captured data and fire SQL suery to store this result into SQL Server. consider it as your reference if you want, and then you can better start your own thread for specific questions.
Hope this helps.

---

]]>