Our dataset is large. We made views from tables in SQL Server to minimize the amount of data. But then the performance of our Power BI dropped. It looks like the SSAS tabular model is somehow using the code of the view instead of te result of the view.

Can anyone help us understand this?

Example 1 - when using this view:

CREATE VIEW [dim].[vw_PlaylistJaar] 
AS
    SELECT DISTINCT
        broad_date.Year AS jaar
    FROM
        Datamart.fact.Playlistline pls
    INNER JOIN
        datamart.dim.Playlist plt ON pls.Playlist_SID = plt.Playlist_SID 
    INNER JOIN
        datamart.dim.Date broad_date ON broad_date.Date_SID = pls.DateBroadcast_SID

The resulting table is a list of years; eg 2020, 2021, 2022.

This view in an input in our SSAS tabular model for the table PlaylistJaar with column jaar . This table has an one to many relationship with other tables in the model.

When we deploy the SSAS tabular model, we get the following error:
Column 'jaar' in Table 'PlaylistJaar' contains a duplicate value '2020' and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table

It looks like SSAS is not using the result table that is the distinct values of Year, but is using the code behind the view.

We then loaded a table with unique Year values in the same SSAS model and didn't get an deployment error.

Example 2

We also have a much larger use case where we remove a large amount of rows in the view. The SSAS tabular model uses this view and we have Power BI to visualize. However, it looks like the performance of the Power BI is better when we use the underlying table and remove the rows in SSAS tabular model.