Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
speedramps
Super User
Super User

Best choice of data integration tools

I am looking at options for a data warehouse / data integration tool for a medium sized company. Any suggestions, recommendations or comments will be welcomed. Thank you.

 

The company has data on a variety of source systems, which we want to harmonise and show a single version of the truth on Power BI.

 

The system will need to get or receive batch feeds from the source systems and then translate the source system native codes for customer, product and supplier to universal surrogate keys using cached lookup cross-reference tables and SCD type2 (slow changing dimensions).

 

My thoughts so far are:-

 

On premises SSIS and SSAS

Pros:- Tried and trusted for years

Cons:- We desire to use the best data warehousing practices currently available with new cloud technology.

 

Microsoft Data Flows

Pros:- Looks easy and I like the way the queries, data flows lakes, datasets marts, scheduled refreshes and power bi reports can all be managed via the same Power BI service consol.

 

Cons:- Needs premium capacity for the SCD cache lookup functionality.
The cost leaps from a Power BI Pro $9.99 per user to $4,995 Power BI Premium license per month.
The data flows is held as a csv, and there is concern about saclability because.  Will the CSVs cause performance issues?

 

 

Azure Data Factory

Pros:- Looks like a cloud version of SSIS and SSAS.  Any existing SSIS and SSAS procs can be migated.

Cons:- The data Factory usage pricing structure means it is tricky to estimate costs. Also it is complicated to trigger a Power BI API refresh after a Azure Data Factory refresh.

 

 

What do you use and what are your experiences?

 

Thank you

 

 

 

  

2 REPLIES 2
jarthda
Regular Visitor

Hi,

I just want to chime in as I'm using SSIS/SSAS on a VM then porting data stores to individual client Azure SQL. I wanted to look at 'Data Flows' in place os SSIS for some cases. For example CURL GET? Maybe there is a way to consume JSON without writing tsql procedures and CRLs. But I'm just exploring. I like SSIS/SSAS.

 

I agree that Datafactory is challenging to figure out cost. I gave up. Why spend so much time figuring out somelthing I can already do old school.

 

Regarding Dataflex. I don't see what it is exactly. Is it relational storage in the Power Apps platform? If so, how is it in a conversation about an ETL tool? It looks like storage ...     

Greg_Deckler
Super User
Super User

@speedramps - I think you are missing an option, the Common Data Service (CDS) which is now called Dataflex. Pros, your data is now in a place where Microsoft is throwing a ton of investment and immediately available to the entire Power Platform line-up of tools. Cons, really, really new way of doing things.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors