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

[Magatame]

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

[Anonymous · Accepted solution]

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. 

[v-ljerr-msft]

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

[Anonymous]

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?