- 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.
- Getting started with Google Guava
- Java Web開發之道
- 基于免疫進化的算法及應用研究
- HTML5入門經典
- Mastering Android Development with Kotlin
- Learning Concurrent Programming in Scala
- 區塊鏈技術與應用
- Android Wear Projects
- JavaScript動態網頁編程
- Flask Web開發：基于Python的Web應用開發實戰（第2版）
- Python 3 Object：oriented Programming（Second Edition）
- Head First Kotlin程序設計
- 程序員面試金典（第6版）
- 美麗洞察力：從化妝品行業看顧客需求洞察
- C#程序設計基礎與實踐