- Microsoft Tabular Modeling Cookbook
- Paul te Braak
- 981字
- 2021-11-12 16:24:14
Managing connections and tables
The goal of the model designer should be to produce succinct models that are easily maintainable. This implies the re-use of objects (and structures) wherever possible. We have seen that it is relatively easy to import data into the model; however, the designer should also think about the maintenance of the model in the future. This recipe looks at how an existing model can be extended by adding additional data, and how a table can be altered once inside the model. This recipe is motivated by the maintenance of an existing table—that is, how do we change the import of a table which has already been specified? Additionally, as a point of practice, the modeler should re-use an existing connection that has been created, rather than create additional connections by continually importing tables using the same database settings.
Getting ready
This recipe uses the model that has been created in the prior recipe Importing data from databases.
How to do it…
- Open the workbook that was developed in the recipe Importing data from databases, and launch the PowerPivot window.
- In the Design tab, click on the Existing Connections button to show the connections in the model.
- Double-click on the connection Sales Data – SQL, ensure that the Select from a list of tables and views to choose the data to import radio button is selected, and click on Next.
- Select the
sales_headertable by checking the checkbox next to the table name and click on the Select Related Tables button to select all tables that relate to thesales_headertable. - Deselect the
sales_detailtable and click on the Finish button to import the remaining tables. - Click on the Close button to close the Table Import Wizard window.
- Switch to Diagram View to ensure that relationships have been created between the
sales_header,territory,currency, andcustomertables. - Create a relationship between the
sales_headerandSales Detailtables, and delete theSales Headertable that was created in the recipe Importing data from databases.Tip
It is not necessary to correctly identify the source and related tables while defining a relationship. The model makes an estimation about which table is the reference table based on the cardinality of data in both the tables. While defining relationships in the Diagram View, PowerPivot will correct an invalid relationship (one which points in the wrong direction) based on this inference.
The
currencytable shows the currency ID in the currency name (as shown in the next screenshot). As the designer, we would like to suffix thecurrency_idfield to the name, so that it includes the code within the name. For example, the name Emirati Dirham should appear as Emirati Dirham (AED). While we could create a formula for this within the model (see the Adding fields to tables recipe in Chapter 1, Getting Started with Excel), we want the model to be succinct and only show a single name. In order to do this, we can change the underlying table definition to include a custom calculation. Prior to the change, the table looks as shown in the following screenshot:
- Select the currency table and click on the Table Properties button in the Design tab. A new Edit Table Properties window will open.
- Change Table Preview to a SQL view by selecting Query Editor from the view drop-down box.
- Replace the existing query with the following code:
SELECT currency_id, currency + ' (' + currency_id + ')' ascurrency FROM [chap2].[currency] - Click on the Validate button to ensure that the SQL statement is valid.
- Click on the Save button to reload the data and return to the
currencytable.
How it works…
Importing data through the Get External Data group of menu items is done by first creating a new connection and then creating table definitions on the connection. Importing data with these buttons is suitable when there are no existing connections in the model and when new data is required. However, when an existing connection in the model can be used to add additional data sources which use the existing connection, it makes more sense to re-use that connection (rather than creating a new one). This improves model manageability since the number of connections in the model is reduced. If the connection properties change (for example referencing a different server), the property only has to be changed in one connection.
The definition (query) used to define the table can also be edited through its Table Properties.
There's more...
The Table Properties editor includes a query designer which can be used in lieu of writing SQL. In order to use this, click on the Design button in the Edit Table Properties window. This will invoke the editor (which usually defaults to a text view). However, the developer can click on the Edit as Text button in the designer to switch to a visual designer.
The definition of tables within the model holds information about the Source and Model definitions. Source refers to the underlying object (its physical structure), whereas Model refers to the semantic definition (what the end user sees). An example of the application of this can be seen when a column is renamed within the model (see the Managing the appearance of tables and fields recipe, in Chapter 1, Getting Started with Excel). The Edit Table Properties window allows us to toggle between these two views of data, by selecting either the Source or Model radio buttons, as shown in the following screenshot. This is only available for tables that have not been altered by defining a custom SQL.
- Java多線程編程實戰指南：設計模式篇（第2版）
- 軟件架構設計：大型網站技術架構與業務架構融合之道
- Python機器學習算法與實戰
- Raspberry Pi Home Automation with Arduino（Second Edition）
- Android玩家必備
- Processing創意編程指南
- C# Multithreaded and Parallel Programming
- Distributed Computing in Java 9
- CodeIgniter Web Application Blueprints
- 數據分析與挖掘算法：Python實戰
- Software Development on the SAP HANA Platform
- iOS Development with Xamarin Cookbook
- Illustrator CS6中文版應用教程（第二版）
- Flask Web開發實戰：入門、進階與原理解析
- JavaScript重難點實例精講