jdb五龙捕鱼怎么下载

書名： Microsoft Tabular Modeling Cookbook
作者名： Paul te Braak
本章字數： 1317字
更新時間： 2021-11-12 16:24:13

Importing data from databases

Although tabular models support the import of data from a variety of sources, a relational database is still considered to be one of the primary methods of obtaining data. Unlike other sources (such as text files), where the structure may change from time to time, the data from a database is preferred because it conforms to a schema which is expected to remain constant. Furthermore, most operational systems store their data in a relational database format, and therefore, the database becomes a suitable source of data.

Tabular models may extract relational data through a generic connection on the machine, for example, Open DataBase Connectivity (ODBC), which is a standard method of data access, or by a connection based on a native driver (specific driver) stored in the model.

Tip

An ODBC connection also stores a connection within the model. However, the actual connection to the database is managed by the ODBC connection on the machine (rather than the tabular model).

Wherever possible, the native driver should be used, since the driver supports the underlying Database Management System (DBMS) rather than the generic ODBC standard.

This recipe extracts data from a SQL Server database.

Getting ready

This recipe simulates the loading of the sales data from an operational database, commonly referred to as Online Transactional Processing (OLTP), based on the following schema:

This schema implements a standard invoicing structure where a header record (sales_header) contains generic information about the sale, and the detailed records (found in sales_detail) collect the line items for the sales. The database implements foreign key constraints to enforce referential integrity.

Our goal for the recipe is to only import all sales (and related information) that occurred in Australian dollars. Australian dollar sales are determined by currency_id of AUD in the sales_header table.

How to do It…

In order to complete this recipe, the (SQL Server) database should be restored. The information on how to do this is available from the online resources with instructions in the Appendix, Installing PowerPivot and Sample Databases. Once this has been completed, we start from the PowerPivot window.

Open a new workbook and launch the PowerPivot window.
In the Home tab, select From SQL Server from the From Database drop-down list in the Get External Data group, as shown in the following screenshot:
The Table Import Wizard window opens to create a new connection within the model. Populate the wizard's options with the following values:

Tip

To help with the selection of Servers of Databases, the drop-down boxes can be used. However, while selecting a server, the response of the dropdown may be slow, as all available servers on the network are polled.
Ensure that you can create a connection to the database by clicking on the Test Connection button (a message box should return with the message Test Connection Succeeded).

Tip

While connecting to a SQL Server, we have two authentication methods to choose from (Windows or a SQL Server). Windows authentication requires your user account to have access to the database (since the database is restored from the online resources, we assume that you have the appropriate permissions), whereas a SQL Server authentication requires a SQL Account (an account on a SQL Server with its own username and password). The preferred method of authentication is always Windows.
Click on the Next button to specify how to define the data that will be imported. 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.
Activate the sales_detail field from the Chap2 schema table by checking the checkbox for the sales_detail table. Rename the table to Sales Detail by changing the name in the Friendly Name column.
With the sales_detail table row still selected, click on the Select Related Tables button. This will check the products and sales_header tables. Rename these tables to Products and Sales Header.
Ensure that the sales_header table row is active (the active row is dark blue) and click on the Preview & Filter button. A new window will open, which shows the available columns and filters on the table (as a grid). A column can be removed from the import by deselecting it.
Restrict the sales_header table to import only AUD sales by selecting the AUD currency_id from the column's drop-down box and clicking on the OK button.
Click on OK to return to the Select Tables and Views dialog box of the Table Import Wizard window.
Click on the Finish button to import the data, and then on Close to exit the Table Import Wizard window.
Switch to Diagram View to confirm that relationships have been created between the Sales Detail, Products and Sales Header tables.
Create a pivot table which shows currency_id from the Sales Header table on rows and (the implicit measure) Sum of quantity as values.

How it works…

The Table Import Wizard window does two things each time it is invoked. Firstly, it creates a connection (which is a definition) within the model to define how the database is connected. Secondly, it creates a table definition for each table that was defined by the Wizard.

We can see that there are two distinct steps. However, since the first step in this process creates a connection when the Wizard is invoked again, and the same connection name is used, an error will be shown. This is because each connection name must be unique. We can demonstrate this by trying to reproduce the steps in this recipe (in the same workbook). Before the Table Import Wizard window allows you to select available tables from the connection, the Wizard will prompt you with a message that a data source with the same name already exists (and a different name must be specified in order to continue).

The Select Tables and Views window of the import allows you to define the table name within the model (note that the table will be called Friendly Name within the model) and filter the data that will be imported. Further, by using the Select Related Tables button, the Wizard will include any table that has a foreign key relationship with the selected table. This can be a very useful feature; however, one potential downside of relying on the Select Related Tables button for the identification of related objects is that the functionality is non-recursive, and it will apply only to the immediately selected object. This means that the related tables will not be identified. A generic ODBC driver does not allow for the recognition of relationships between tables.

The Preview Selected Table window allows you to define new column names and filter the data that is imported for a specific table. However, when a filter is applied to a table, the filter is only applied on the table that is imported—it is not applied to tables related to the table being imported. This means that the data in a table that is related to a filtered table is not filtered simply because its related table is filtered. This is the reason why the pivot of currency_id includes a blank row.

Note

Tabular models do not allow as much flexibility for defining missing data as multidimensional models do. If the data in one table is missing from another, a missing value (a blank) is substituted into the other table. Unlike the multidimensional model, the label cannot be changed.

In contrast, there is no requirement to explicitly define error handling in tabular modeling. All the data is imported into the model (regardless of any requirement for a related value in another table).

There's more...

The connections that have been created in the tabular model can be seen by clicking on the Existing Connections button in the Design tab. All connections that exist in the model will be shown under the Power Pivot Data Connections group.

官术网_书友最值得收藏!

Microsoft Tabular Modeling Cookbook