When to create a data model in Power BI vs. When to create a database in SQL Server

[Anonymous]

I will be building a report for freight cost that must span multiple reports and multiple sources. I will get the invoice data in one file. I will connect this to the carrier cost files (4 separate files) and then I will need to connect this to the manifest data (separate file). From there I will need to limit that data based on the pack station data (6th file type), which I can connect to the manifest data.

What I am wondering is what's the best tool to build this? I was leaning towards importing my files into SQL Server to assemble and link everything. From there, I would think it would be best to upload into Power BI (unsure of direct query method or another method). 

I tried my best to search for this information but I didnt see that anyone had asked a question like this. Appreciate any insight someone can give me.

[themistoklis · Accepted solution]

@Anonymous 

It highly depends on the ETL that you are planning to do and the size of the data.

Also it depends on the data retention period (data history) and the processing power that you will use for the ETL.

So you need to choose between a Data Warehouse and PowerBI I would say.

In a Data Warehouse you combine data from different sources, you do aggregations that will be later used for reporting purposes. You keep historical data and you choose the type of ETL that will be used on tables (e.g. using FULL load of tables or Deltas).

Also if your Data Warehouse sits on a powerful server with +XX cores and ++RAM memory then it is best to do the heavy ETL there and then load the consolidated-aggregated-combined data to PowerBI.

At the end of the day it will be your decision to choose between a Data WareHouse, PowerBI or both