Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
analyticsyc
Frequent Visitor

Best Practices for Sustainable Data Transformations

I work with Syspro (an ERP system) which sits on top of an SQL Database. It records all of our company's sales information, and I use PowerBI to tie together multiple tables and create reports for different departments. I currently have 1 main model, which I adjust visuals for different end users. As the number of reports has grown, I am struggling to keep changes consistent throughout all my models. I make my data transformations in Power Query, which is great, until I need to make the same change across 10+ different files. 

 

I'm hoping someone has advice on how to create a sustainable method of data transformations rather than using Power Query. My initial thought was using SQL to transform my data, and then linking all my models to that transformed data. I would store the transformed data in Azure. That way, when I need to make a change (say adding a calculated column), I only have to make the change in SQL and all my PowerBI files are automatically updated the next time they refresh. One requirement is that wherever this transformed data is stored, I need to be able to schedule refreshes through power automate. Using SQL is just my initial idea though, and I'm interested in hearing other's opinions on what works best.

1 REPLY 1
lbendlin
Super User
Super User

Read about data warehouse, data lake and (the newest fad) data lake house.  

 

Microsoft offer Dataflows if you want to apply your transforms once.  They are also pushing datamarts which are a very tortured way of giving direct query access to dataflows.

 

If your number of data domains is small and you think you can do the modeling then keeping everything in SQL server is not out of the question.  For larger endeavors look into Purview or Collibra.

 

Don't forget the oldie but goldie - SSAS.  That could provide you a multi dimensional data model that might work for your needs.

 

And lastly think about auditing and recoverability.  Think very hard about these.  It's "easy" to create a reusable data model but it is very very hard to keep it sane.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.