Is SSIS the usual way we sync the data from Master Database(view) to EDW?

Question

Hello All,

In my case, I created master databse serivice on source db and import the data from source tables as the entiies controlled by MDS. Meanwhile, I created views to expose the data changed/deleted/new-added.

Now my question is whether SSIS is the most usual way we sync the data from MD views to dimMensions in EDW?

By  this, I have to invovle ETL development effort?  If Masetr DB and EDW Db are in two boxes? how to qucikly turn around to sync the data?

Thanks for your suggesion.

 

---

Derek

Answer 1

Hi Derek,

Generally, we usually load data MDS using SQL Server Integration Services(SSIS), with SSIS you can load data into the staging area tables and then in MDS via the import process. SSIS can consume data from MDS and load data into it using either the Data Flow Task or Execute SQL Task. 

For more information about this topic you can refer to this blog for detail information http://johanmachielse.blogspot.com/2011/02/master-data-services-loading-data-using.html 

Thanks,

---

Challen Fu

TechNet Community Support

Answer 2

Hello Challen,

Thanks for your reply. However, I might not make my case understood.

I am not asking how to load data into MDS DB hub but I am seeking the answer that how make the changed data from mds DB hub data to those who are interested, typically like data warehouse demension tables.

 

---

Derek

Answer 3

if I am understanding your question correctly, in my experience, SSIS is a popular and the most usual way to sync the data between MDM and a data warehouse or data mart.  There is not a more 'usual' way to do they sync. 

Answer 4

Making sense.

What i am conplaining is that there is an entry "Intergration View" in MDM portal. However, by this entry , business users , after changing the data in excel, they dont have any chance to know when the chnaged data can flow to data warehouse. If entrying this entry "Intergration View", users can know what is the next time sync or they can kick off the sync package, that will be fancy!

---

Derek

Answer 5

Yes, that would be fancy.  I think any changes to the MDS interface would be unsupported.  You're best bet may just be to communicate to them that your refreshes run every hour (or whatever your interval is).  They would then know that they need to wait until they see their changed data in the data warehouse.

Answer 6

I agree the proper trainging to USER and inform them what is going on behind the scene.

However, I would love to see developers can build the SSIS strong enough and grant the execution permission to end users who understand the critical businesss.

---

Derek

Answer 7

Hey Derek, have you tried to look at the versioning feature and flags? If you want to enable your users to commit versions, then your downstream applications can consume data based on the Current Version Flag.

---

http://www.henryong.com