Fabric & Data Sharing - Data warehouse, Lakehouse and Semantic Model Updates

[tomperro]

I'm setting up a data reporting system in Fabric and need to share a unified view of data from Salesforce with users in other departments. I'm looking for guidance on whether my current approach is sound and how to implement daily updates.

Here's what I've done so far:

• I've established a Fabric Warehouse that pulls two tables from Salesforce: Contacts and Tests.
• Within this warehouse, I created a new table called CombinedTestInformation. This table contains a selection of fields from both the Contacts and Tests tables.
• I'm using dataflows to connect to Salesforce and bring in the Contacts and Tests data.
• My goal is to create a semantic model from the CombinedTestInformation table, which I can then share with other departments for their reporting needs.

I have a few questions about this setup:

1. Is this the recommended method for sharing transformed data with other users in Fabric?
2. I created the CombinedTestInformation table using a CREATE TABLE SQL statement. How can I configure this combined table to update automatically on a daily basis?
3. When connecting Power BI Desktop to the CombinedTestInformation semantic model, is it possible to use the table view to inspect the data within the tables?

[pankajnamekar25]

Hello @tomperro 

I will suggest to explore in details article by microsoft

What Is Data Warehousing in Microsoft Fabric? - Microsoft Fabric | Microsoft Learn

Thanks,
 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.

[Akash_Varuna · Accepted solution]

Hi @tomperro  I think your approach is good now answering your questions

1. Is this the recommended method? Yes, using a Fabric Warehouse with dataflows and creating a semantic model is a good practice for centralized data sharing.

2. How to update the table daily? Use Fabric pipelines or schedule a SQL job to refresh the CombinedTestInformation table automatically.

3. Can I inspect data in Power BI? Yes, in Power BI Desktop, you can view the data within tables in the Model view’s Data pane.

[tomperro]

I created a warehouse and I am pulling data from salesforce into my Contact table via dataflow. 

The dataflow is creating duplicate rows when I perform a refresh.  

How do I avoid these duplicates?

It seems like it is appending rather than replacing all of the data.

[v-sathmakuri]

Hi @tomperro ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @Akash_Varuna  and @pankajnamekar25  for the responses.

Since duplicate data is being inserted into the final table, consider creating an intermediate (staging) table.
In your data flow activity, after performing all necessary transformations, insert the data into this intermediate table instead of directly into the final table. Then, use a stored procedure to compare the intermediate table with the final table using a primary key or unique identifier.

Based on your requirements, the stored procedure can  Insert new records that don’t exist in the final table or Update existing records if there are changes.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you!!

[v-sathmakuri]

Hi @tomperro ,

May I ask if the provided solution helped in resolving the issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you!!

[tomperro]

@Akash_Varuna 

Thank you for the reply.

1. How to update the table daily? Use Fabric pipelines or schedule a SQL job to refresh the CombinedTestInformation table automatically.

   How do I create a SQL job in Fabric?

2. Can I inspect data in Power BI? Yes, in Power BI Desktop, you can view the data within tables in the Model view’s Data pane.

   I have connected Power BI desktop to this semantic model and I do not see the data in the table view. Am I doing something wrong?