2/20/2024 0 Comments Rename tabular database![]() The views can include transformation of data. The views created should include an explicit list of columns, and should not be a generic one such as: You just have to deploy a compatible version of the views that use the new structure, and notify to the BI analysts who owns the data model that they might use a new version of the data, coordinating with them how to provide the new structure (for example, by changing existing views or by providing different views). You will not delay the deployment in production of certain database changes, because you do not have to synchronize the deployment of a new version of all the existing reports. If you modified the database structure, probably you want to reflect this change to the reports, but with a different timing. Changing the view to keep compatibility with existing reports is usually a first temporary step. Keeping all the views for a data model in the same schema simplifies the tracking of the dependent reports. For example, in SQL Server you can use built-in features (such as View the Dependencies of a Table) or third party tools (such as SQL Dependency Tracker from Red Gate). Moreover, it is much easier to track dependencies between views and tables in a single relational database. Before publishing in production a change in the database structure, it is possible to adapt these views so that they will continue to work returning the same content, without breaking the refresh of existing reports. When you import the tables in Power BI, remove the name of the schema and keep the name of the view only.īy following this best practice, you declare in the database what are the tables and columns used in a report, so that the database administrator is aware of existing dependencies from the database itself.Include in the view only the columns that are useful and will be used in the Power BI data model.Create one view for each table you want to create in the Power BI data model within that schema.Create a schema for a certain data model: for example, it could be the name of the data mart, or the name of the group of reports that will share the same data model.Creating specific views for each data model correspond to the introduction of an indirection layer who simplify the change management of the database structure. Even if a dependencies analysis would be possible from a technical point of view, today we do not have a standard tool and procedure to do that. The real issue is that nobody knows how many reports can be affected by a certain change. ![]() For example, renaming a column or a table, or changing the cardinality of a table, are all operations that require a correspondent change to the Power BI data model. Over time, certain changes to the database structure might corrupt an existing report. The reason is that this creates a strong dependency between the physical data model and the report. ![]() If you are getting data from a relational database, such as SQL Server or Oracle, you should never import a database table directly in your data model. Use viewsĪlways import views and never import tables in a data model. ![]() The demo file you can download (at the end of the article) contains a sample Power BI file and the corresponding views defined in a SQL file for AdventureWorksDW. You should adapt these best practices to your specific scenario, looking at how to achieve the goals that are the reason for a certain pattern more than barely apply it without considering the pros and cons of each choice.Įven if the article mentions Power BI, all the best practices described are valid for Power Pivot and Analysis Services Tabular models, too. Not all of the suggestions described can be applied to all of your data models.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |