Is Power Query can be used as as an ETL Tool in large Enterprise Products with multiple Integrations data sources

Power BI Data Model
1

Hi Team,

This is not a Tactical question as such no PBIX has been attached.

It s a Strategic or BI arch question on Power query for Business Intelligence projects within a large enterprise settings/Product.

Let me set the context here , as I herd and agree , Power Query is the most affortable , verstile sort of ETL tool in Business scenario where Product is simple , May be it has 1-3 integrations , a Databases etc where we can get the data etl using power query and create models in Power BI (Pls correct this assumption if need)

[Scenario where the question is applicble ]
However, I am only referring to large scale products , You can consider eg of Amazon UI, Order Processing system , Order fullfillment system etc.-The architectural structure is comples with multiple interfaces or multiple integration with different(independent) systems

Long story short -What I have noticed in such products, we need to integrate data from different sources using Standard ETL Tools like (Informatica, SSIS, Talend etc) into a Data warehouse (eg. Tera data etc or similar)

From the Dawarehouse usually OLAP Cube is created in SSAS

Now we can Connect to Cube using Power BI to build reports .

Side Note : Please see -above comments might not be precise, so pleasefeel free to correct me if you got my intention behind the question below - I am not or never worked professionally as BI Professional , Its my hobbie that I am learing Power BI.
However I do work with BI Team for reporting requirements that I create for my product.

[Question context begins]

What keeps me wandering is that -There are huge now of Power Query questions either in Enda forum or otherwis public microsoft forum , certianly Power query has some sort of holy grail capability.

At the same time, on the other hand, I have never herd such Products using Power Query.
(Pls note : Pls dont get emotional :smiley:, I already admitted power query is definitely a famous tool, but only I have not seen being used in large projects ; so i want to reaffirm if this observation is valid or not via this thead)

[So exactly once again my question is ]

Qn 1 )In the example scenario mentioned have - Do company use Power Query ?

1a) If your answer is yes , then pls clarify at what stage of BI lifecycle
e.g From Data ware house to SSAS cube
or from Source to Data Warehouse.

1b) Can you pls clarify if Power query can integrate data from multiple heterogenous sources -Different data bases -SQL +NOSQL etc

2

Hi @Dhrubojit_Goswami,

I trust you’ll find this an interesting read…

BTW this is part of a series by Paul Turley, you might also want to go over the other articles as well :wink:
I hope this is helpful

2 Likes
3

Hi @Melissa Other Patrons

Thank you fir the reference that is indeed a great article

Firstly, Sorry for my ignorance

As per article power query can be used to connect large no of sources directly and we can build model directly in power or in Azure or in house SSAS on the fly without any additional data warehouse as middle tier ( Yes we may substitute it with more global data lake ) .This answers when and how ,(But is it always the case with Power Query)

My qn still remain same in sense :When not ,when can’t be type:
Can power query plus ssas be used for etl and builder BI models for product of any size and complexity ?

If yes ,then I am getting confused thinking why would organization pay hugely for tools like informatica power enter and talend pro ?

In other words ,What is the use case in which org will use power query as etl tool to build model VS more well known industry standard informatica or talend

Also, Gartner ETL tool Ranking don’t have any mention of power query despite being so popular , this is another reason that make me think .

Please help me understand the same on use case difference as noted above

4

Bumping this post for more visibility.

5

Hi @Dhrubojit_Goswami

Power Query itself is a very useful ETL tool, that can be used to perform large number of operations. We shall primarily use Power Query directly when Data is structured, require minimum resource specific transformations .

However Power Query has limited capabilities when it comes to Data Cleansing, transformations etc. Few limitations, when Power Query shall not be used.

  1. Data is unstructured - Power Query works well with structured data. However if the Data is unstructured, then Power Query has limited cleansing ability.
  2. Large Dataset - Power BI has limits on Dataset that can be imported based on licensing. If trying to import large dataset, then it may not support import and if go with Direct query then performance will get impacted.
  3. Complex transformations - Power Query can be resource consuming. If performing any complex transformations like Merge/Sort with large dataset, then performance can be drastically reduced.
  4. Complexity - Power Query is a complex tool. it’s Ok to perform transformations using UI, however mostly in BI projects there will be requirements to perform transformations that will require Advanced knowledge of Power Query that very limited users possess.
  5. Auditing/Error Handling - Power Query has limited error handling capabilities and also in most cases gives generic errors that can be difficult to interpret.
  6. Rerun-ability - Power query doesn’t have option option to rerun a single table/query on Service. Even if there is a failure in a single refresh, then whole Dataset needs to be Refreshed.

Above are few examples where Power Query may not be the best Tool to use and perform ETL operations.

Thanks
Ankit J

2 Likes
6

@ankit Thank you for your response, That gives me prospective that my assumptions where some where in line with your response that I want to reaffirm

Are you saying that in those complex scenarios we prefer to use enterprise grade etl tool like informatica ?

7

@Dhrubojit_Goswami - Yes, In scenarios involving complex transformations or where lot of Data cleansing is required. Along with other points that I mentioned.

Thanks
Ankit J