Best Practice for Editing Semantic Model

[Anonymous]

Hi,

I am currently working on a project in Power BI Desktop. The data source is SharePoint (from Power Apps), and I imported it into Power BI service. When I connect to the Power BI semantic model in Power BI Desktop, there is no option to edit or import, and in table view I cannot edit since it is not in import mode. I want to edit the data types of a few of the columns, and I also want to add calculated columns and measures into the semantic model.

My question is what is the best practice to manage this kind of semantic model? Should I edit in Power BI service directly and should I make a copy of the original semantic model? Should I only do the minimal transformation in PBI service (data type only) or should I also create columns and measures in PBI service?

Thanks.

[danextian · Accepted solution]

Hi @Anonymous 

It looks like you're connecting to a semantic model using live connection. This type of connection doesnt allow editing the model so changes to it other than creating new measures must be done in the model itself.  For changes that do not require  ETL, you will be able to edit the model in the service (if enabled by your tenant admin). For changes that requires the query editor, you will need to edit in Desktop and republish the model.

Should I edit in Power BI service directly and should I make a copy of the original semantic model?

Making a copy fo the original semantic model defeats the purpose of being able to create thin reports out of it.  You will  end up maintaining too many semantic models that serve the same purpose.

Should I only do the minimal transformation in PBI service (data type only) or should I also create columns and measures in PBI service? You can't do this with live connection so it is necessary to do the needed ETL in the  semantic model itself and ideally as upstream as possible (at the source).

If you need to bring in more data but want to use the existing semantic models, you can connect to those using direct query. If initially using a live connection, adding another data source will prompt you to create a local copy of the model. RLS and permission from the referenced semantic model will still apply.

[Ritaf1983]

Hi @Anonymous 

It’s important to clarify whether your semantic model is using DirectQuery or Import mode, as the capabilities and best practices differ between the two:

DirectQuery Mode:

1. Limitations:

   • You cannot edit data types or add calculated columns in Power Query or Desktop.
   • Your transformations are limited to creating measures (DAX) in Power BI Desktop.

2. Recommendations:

   • Use DAX in Power BI Desktop to create measures.
   • If changes to data types or columns are necessary, make them in the source system (e.g., Power Apps or SharePoint).
   • DirectQuery is useful for near real-time updates, but it comes at the cost of flexibility and performance.

Import Mode:

1. Advantages:

   • You can fully utilize Power Query for data transformations, such as changing data types or creating calculated columns.
   • Once the data is imported, performance in Power BI is generally better because queries are run against the imported data instead of the source.

2. Recommendations:

   • If real-time updates are not essential, consider switching to Import mode for more control and better performance.
   • After switching to Import, you can:
     • Use Power Query for data transformations.
     • Add calculated columns and measures in Desktop.

---

General Best Practices:

• Power BI Desktop:

  • Perform all modeling tasks, including data transformations and creating calculated columns or measures.
  • Use it for detailed development and testing.

• Power BI Service:

  • Use primarily for publishing, managing, and sharing your reports.
  • While model editing capabilities in Power BI Service have improved, they are still limited compared to Power BI Desktop.

---

If your scenario permits, I recommend using Import mode, as it allows greater flexibility for transformations and better performance for most use cases. However, if you need real-time data, DirectQuery remains a valid option, with the trade-offs outlined above

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[danextian · Accepted solution]

Hi @Anonymous 

It looks like you're connecting to a semantic model using live connection. This type of connection doesnt allow editing the model so changes to it other than creating new measures must be done in the model itself.  For changes that do not require  ETL, you will be able to edit the model in the service (if enabled by your tenant admin). For changes that requires the query editor, you will need to edit in Desktop and republish the model.

Should I edit in Power BI service directly and should I make a copy of the original semantic model?

Making a copy fo the original semantic model defeats the purpose of being able to create thin reports out of it.  You will  end up maintaining too many semantic models that serve the same purpose.

Should I only do the minimal transformation in PBI service (data type only) or should I also create columns and measures in PBI service? You can't do this with live connection so it is necessary to do the needed ETL in the  semantic model itself and ideally as upstream as possible (at the source).

If you need to bring in more data but want to use the existing semantic models, you can connect to those using direct query. If initially using a live connection, adding another data source will prompt you to create a local copy of the model. RLS and permission from the referenced semantic model will still apply.

[Anonymous]

Thanks for the explanation. The source is Power App RSS and the export options are limited, either to files or export to Power BI. I want to be able to refresh the semantic model, so that's why I choose to export to Power BI. In this case I only have the option of connecting through direct query. Is there a way where I can import the data from the semantic model to Power BI desktop?

[123abc · Accepted solution]

You’re welcome! Given your scenario, here are some steps you can follow to import data from the semantic model into Power BI Desktop:

1. Use Power BI Service Live Connection:

   • In Power BI Desktop, go to Home > Get Data > Power Platform > Power BI datasets.
   • Connect to your published semantic model in the Power BI service. This allows you to build reports using the data, relationships, and measures from the semantic model...

2. Workaround for Import Mode:

   • If you need to work in import mode, you can use a workaround by connecting through the Analysis Services connector:
     • In Power BI Desktop, go to Home > Get Data > Analysis Services.
     • Enter the server address of your Power BI service workspace (this requires a premium capacity workspace).
     • This method allows you to import tables from the semantic model.

3. Export to Excel and Import:

   • Export the required tables from the Power BI service to Excel.
   • In Power BI Desktop, use Get Data > Excel to import the tables.

These methods should help you manage and refresh your semantic model effectively in Power BI Desktop. If you have any more questions or need further assistance, feel free to ask!

Please also follow below link ... May it will help you.

Connect to and transform data for a semantic model  – Tabular Editor Blog

Connect to semantic models in the Power BI service from Power BI Desktop - Power BI | Microsoft Lear...

Solved: Import Table from Power BI Semantic Model - Microsoft Fabric Community

[123abc]

When working with Power BI semantic models, here are some best practices to consider:

1. Edit in Power BI Service: Since you cannot edit the model directly in Power BI Desktop when it’s not in import mode, you should use the Power BI service to make your changes. The Power BI service now allows you to edit data models directly, including modifying data types, cr...1.

2. Minimal Transformations in Power BI Service: It’s generally a good idea to perform minimal transformations in the Power BI service. Focus on essential changes like data type adjustments and creating necessary calculated columns and measures. This approach helps maintain model performance and manageability2.

3. Create a Copy of the Original Model: Before making significant changes, create a copy of the original semantic model. This ensures you have a backup in case you need to revert to the original state2.

4. Use Descriptive Naming Conventions: Make your model easily understandable by using clear and consist...3.

5. Document Your Model: Maintain clear documentation of your model, including relationships and calculations. This will facilitate future updates and collaboration3.

By following these practices, you can effectively manage and optimize your Power BI semantic model. If you have any more questions or need further assistance, feel free to ask!

Edit data models in the Power BI service (preview) - Power BI | Microsoft Learn

Mastering Power BI Semantic Models: A Beginner's Guide to Building Data-Driven Insights - Datum Disc...

Semantic models in the Power BI service - Power BI | Microsoft Learn