hi , i am assigned for designing a new OLAP DB from existing OLTP database. now , the biggest question in my mind is that how can i update my OLAP on every updation in OLTP. please suggest, your advise is valuable for me. thanks in advance,Regards, Shanu

Hi, what do you mean with "every updation"? You mean every day? Or do you want to have the data in OLAP DB immediately after it's inserted/updated in OLTP? If this is what you want then it'll be hard... If you want it periodically, check this thread: http://social.msdn.microsoft.com/forums/en-US/sqlanalysisservices/thread/e7ed5062-34cb-4d98-87c7-456e55005316/ David.

Hi Shanu_Shri, If you update your OLTP database using SSIS you can use a "Analysis Processing Task" in the Control Flow for reloading your OLAP cube. Grtz, Ivo

Hi, When you use OLAP you use it for reporting and decision making purpose and it always include historical data it is not real time database and this will give you performance. So you must use your SSIS to achieve this purpose (cleaning your data and transform it) if there is no transformation you must use SQL server agent to schedule job for your task. if you use SSIS after you create your package you must schedule it to execute at a time you want say every 5 minute or 1 hour and so. I strongly do not recommend you to update it every update happen in your OLTP. because of this the select statement use share lock on your rows and there is lock escalation. so your OLTP will be slow because of lock. if you use select statement with NOLOCk your OLAP will not be real time. and there are many. I hope this is helpful. Elmozamil Elamir MyBlogElmozamil Elamir Hamid http://elmozamil.blogspot.com

hi i know thats huge overhead on performance , but i want to know the all possible ways of doing this . looking for your kind response. many thanks Regards, Shanu

You can also consider "real-time partition" concept described in the Kimball Data Warehouse Toolkit. In a nutshell: 1. Partition the OLAP cube into set of "normal" partitions refreshed daily (define MOLAP as storage mode). 2. Create one partition with ROLAP or real-time HOLAP storage mode referring dedicated SQL table just for this partition. 3. Use CDC or triggers (NOT RECOMMENDED) to populate data in the SQL tables used for ROLAP partition. 4. Every night move data from ROLAP to regular fact tables. It is simple if there are only new incoming fact records, but in case of updates of existing facts it gets more complicated (you need to compensate for existing MOLAP facts in ROLAP partition). Also you can consider using ROLAP or HOLAP for whole cube (use replication and/or CDC to minimize performance impact on the OLTP system), but it will significantly impact cube performance.

Hi Shanu, There are different ways to get this done. Since most of the times we would be wishing to track the transaction tables, enable CDC (avaiable from SQL Server 2008) and use SSIS to push the data on to the data source from where you are building the cube. You need to set a frequency by which the SSIS should pick the data and push it onto the warehouse model. Secondly, have a trigger (NOT RECOMMENDED) which initiates the data movement from OLTP to OLAP database tables. Thirdly have the logic of pushing the transaction data onto OLAP (NOT RECOMMENDED) included as part of your OLTP procedures itself so that the data is pushed across OLTP and OLAP simultaneously. May be you can have a separate stored procedure which pushes the data and invoke it at the end of the OLTP procedure. and the list goes on. In a nutshell, you will have to design your app in such a way that there would be some lead time to push OLTP data to OLAP data otherwise you will end up with whome lot of options which are not recommended :) I second Elmozamil's response and you need to make sure that all the consequences are addressed when you are designing applications for this kind of requirements. Infact, this is a HUGE requirement that industry is looking for to get reports with respect to in-time data. StreamInsight is one feature which would help you to perform analytics for the streaming data (Not applicable for typical OLTP). HTH Regards, Phani Note: Please vote/mark the post as answered if it answers your question/helps to solve your problem.

When you want to capture and report on data as it happens you can try: Microsoft StreamInsight: http://www.microsoft.com/sqlserver/en/us/solutions-technologies/business-intelligence/complex-event-processing.aspxRandy Aldrich Paulo MCTS(BizTalk 2010/2006,WCF NET4.0), MCPD | My Blog BizTalk Message Archiving - SQL and File SSIS Centralize Connection Repository