topic Re: Source to ETL/ELT to PowerBI - the full process - What is the future technologies? in Developer

Source to ETL/ELT to PowerBI - the full process - What is the future technologies?

Magatame — Thu, 04 Jan 2018 14:01:23 GMT

Hi Folks,

Currently in my organisation we extract the data from a source database in SQL Server (2012) and then load this into a staging database holding all changes, from here we then move it into our data warehouse and create our dimensions and facts for manipulation in SSAS. This process is currently done using SSIS.

Is this currently the best approach for the future? I realise that data extraction is very much situational but I feel SSIS and SSAS are no longer the future when we have options such as PowerBi.

My question is this:

If we were to start this project again from scratch how would you recommend we extract and analyse the data (end-to-end) with regards to the technically which is currently being invested in (i.e. PowerBi)

Thanks guys,

John

Re: Source to ETL/ELT to PowerBI - the full process - What is the future technologies?

v-ljerr-msft — Fri, 05 Jan 2018 09:28:06 GMT

Hi @Magatame,

From my point of view, Power BI and SSAS/SSIS are different things. And there're many differences between them. Mainly Power BI is a reporting tool for rendering data and sharing. SSAS is OLAP data source which can feed for reporting. So they cannot be replaced by each other. Instead they should work together, and that could be the future.

Regards

Re: Source to ETL/ELT to PowerBI - the full process - What is the future technologies?

Anonymous — Fri, 05 Jan 2018 10:29:26 GMT

Hello,

we know the technical differences but what are the difference about the result?

Why do we do an actual BI or just use PowerBI from the DB? advantages, inconvenance...?

My advice, a BI (with ETL, DW) is more powerful if you have many DB to cross. That would be all?

Re: Source to ETL/ELT to PowerBI - the full process - What is the future technologies?

Anonymous — Fri, 05 Jan 2018 15:32:06 GMT

Hello,

From my point of view,

I think this would be the best approach

SSIS -->SSAS-->Power BI

Beacause,

SSIS is used for data transformation and it's nothing to do with either of SSAS or PowerBI

SSAS is used to store data in memory, In some cases we could bypass SSAS with PowerBI where your data model size is less than 10GB (which is the max limit that a Power BI file can hold after Compression) but Ideally I would recommond using SSAS as it has no data limits and easy to administrate.

PowerBI is a cloud based emerging reporting tool that could query SSAS cube and bring backs the results in less than a second.

If you are willing to move to Azure (Microsoft Colud) then you may follow this,

SSIS -->Azure SQL -->Azure SSAS --> PowerBI

I think this would be the ideal approach if some has a no objection moving data into Cloud.