Extracting data from data warehouse but need further modeling and cleansing

[ANNING]

Happy New Year all,

Based on the subject topics, I have a few questions.

Background - I would like to create a BI dashboard and the source of data is to retrieve it from SQL Server.

Scenario A - I noticed the schema table I am going to import into Power BI required further data cleansing and transformation. 

• Question 1 - Should I import the table into Power BI and perform the data cleansing and transformation in Power BI or should I perform the data cleansing/transformation in the data warehouse? 

Scenario B - I noticed that the data modelling in the data warehouse does not fit my requirements. 

• Question 1 - Should I perform the data modelling in Power BI? or should the data modelling take place in the Data Warehouse this is the recommended ways? 

Scenario C - I notice that I only need a subset of data records in a few tables in the data warehouse based on business requirements

• Question 1 - Should I perform an SQL statement in the SQL Server query screen? If this is the right way, it becomes a problem because the selected records disappeared once I logged out from SQL Server.
• Question 2 - How do I ensure that my SQL statement is always executed and placed into a schema in SQL Server so that I can import the data from this schema into Power BI to feed into the dashboard?
• Question 3 - Or should I do the unwise way which is to import all the records from all the related schema tables from the data warehouse into Power BI and then perform the transformation in Power BI? 

There are many seasonal and data engineers (note - I am more of a business acumen not technical and I create the visual based on the Excel spreadsheet provided) who proposed that Power BI should only focus on visualisation scope (i.e., DAX) but activities related to backend transformation like the three scenarios above should always take place in SQL Server before you import the data into the Power BI.  I think I want to know from the season Power BI pro how they normally do in those situations. Thank You.

Regards,

Aiyo

[aj1973]

Hi @ANNING 

Your questions are very right to the point. 

Answers:

• Scenario A : Power Query in Power BI is an ETL tool that can help you do all your cleansing and transformations with just simple clicks. It is very powerfull tool.
• Scenario B : Yes you can Model your data into Power BI same as in SQL Server. Using DAX can help you acheive your goals as well.
• Scenario C :
  • Q1 : Know that you can apply your SQL statement right into Power Query. When you Get Data
  • Q2 : Testing is your best way to verify. But I can assure that it will feed your semantic model as you wish
  • Q3 : As said before Power Query is very powerful tool for an ETL and Modeling is very easy In Power BI. However, Even though Importing records into Power BI is the best connection mode but there are situation where some work needed to be done at the Warehouse level. Like using DQ mode if the the fact table is huge or using incremental refresh for performance reasons... It all depends on the BI analysis and situation.

Sorry but Not true. Power BI is no longer just a visualisation tool espacially with Fabric on the horizon. Power Query is an ETL, Modeling and DAX and the semantic model you build can be a Data warehouse itself. Exemple : Building a Semantic model (Dataset), Publish it to the service and give access to developpers to reuse it from Power BI desktop via AAS to also build other semantic models on top of it...