The Master Material Analysis Power BI Semantic Model is configured with both Row-Level Security (RLS) and Object-Level Security (OLS) settings, encompassing key views and tables essential for analysis. The data within the semantic model is already cleaned and transformed. Connecting to the Power BI semantic model proves advantageous as any modifications to the data model will be reflected, ensuring real-time updates and optimizing storage on the Power BI Service. This article demonstrates how to connect to the Power BI semantic model titled "Master Material Analysis v9 Cloud RLSOLS" in both Power BI Desktop and Excel.

Microsoft recently renamed “Power BI dataset” to “Power BI sematic model”. However, it's important to note that the connector in Power BI Desktop and Excel still retains the name "Power BI dataset."

Security settings

Row-Level Security and Object-Level Security are complementary security features that work together to control access to data and objects within a database. RLS operates at the row level, while OLS focuses on object-level access control. The combination of these two features provides a robust and flexible security framework for managing data access in various scenarios.

Row-level security

Row-level security (RLS) controls which rows of data users can see based on their role. This is useful for ensuring that users only see the data that they are authorized to see, enhancing data security and privacy without the need for complex manual filtering in queries or visuals. The semantic model "Master Material Analysis v9 Cloud RLSOLS" applies Row-Level Security based on department numbers.

The below diagram demonstrates how RLS works. Column1 is a security column that stores the department number for each row of data. The green user who can only see data for Dept-0001 will only see the green rows with value “Dept-0001” in Column1. On the other hand, the blue user who can only see data for Dept-0005 will only see blue rows with value “Dept-0005” in Column1. A user can have access to multiple departments.

A Full-access role can see all the records.

Object-level security

Object-level security (OLS) can be used in conjunction with RLS to create a powerful security solution for Power BI reports. For example, a report can use RLS to restrict which rows of data users can see in a report, and then use OLS to hide certain columns from users who do not need to see them.

The below diagram demonstrates how OLS works on top of RLS implementation. The user with “Dept-0005-OLS” role only see rows with “Dept-0005” in Column1 and does not see Column5.

In summary, users in RLS roles only see the rows of data that they are authorized to see. Users in OLS role only see the rows of data and the selected columns that they are authorized to see. A full access role provides unrestricted access to the dataset.

Master material analysis data model description

The following tables and views are included in the semantic model “Master Material Analysis v9 Cloud RLSOLS”:

 

Permissions

Those who have access to the Power BI app “Master Material Analysis” will be able to connect to the semantic model “Master Material Analysis v9 Cloud RLSOLS” in Excel and Power BI Desktop.

How to connect to the Semantic model using Power BI desktop

  1. Open a new blank Power BI Desktop file. Go to Home tab in the ribbon. Click on Get data then select Power BI datasets.

 

2. A OneLake data hub window will pop out showing all available semantic models that you can connect to. In the search bar, search for “master material analysis”. Select “Master Material Analysis v9 Cloud RLSOLS”. Then click Connect.

 

3. After successfully connected, you should see the below list of tables and views show up in the Data pane. By default, the connection mode is “live connection”.

How to connect to the Semantic model using Microsoft Excel

  1. Open a blank Excel file. In the ribbon, go to the Data tab, then select Get data – From Power Platform – From Power BI.

  

2. A Power BI Dataset pane will pop up on the right side of the window. In the search bar, search for “Master Material Analysis”. Then select “Master Material Analysis v9 Cloud RLSOLS”.


3. The below message will show up indicating that the data is loading and a PivotTable will be added to a new sheet.

 

4.Once you are successfully connected, you should see the below screen on a new worksheet.


 Limitations and considerations

Please consider following limitation when using the semantic model to build your own reports:

  • Because the connection is live, modeling is disabled in Power BI Desktop and Excel. User can’t make any changes to the model in Power Query (like renaming columns and adding data from multiple sources).  
  • If modifications to the data model is needed, please reach out to Grace Wei (grace.wei@kemin.com) .
  • Because the connection is live, row-level security (RLS) and object-level security are enforced.
  • If the owner of the semantic model modifies the original report, any changes made to the semantic model will reflect to any reports connected to the semantic model.
  • Only users with Build permission for a semantic model can connect to a published semantic model by using the Power BI Service live connection


 Semantic model data refresh settings

Data in the semantic model are refreshed every hour, please see the diagram below to have a quick description of the data refresh process.