Model servers
Model server configurations
Three main model server configurations:
- Standalone . Suitable for most applications.
- Warehouse-only . Use when Semantic calculations are not required.
Standalone or Warehouse Only Model Server
To verify the connection settings, click Test Connection.
If the connection fails, a red exclamation point will appear. Hover over the exclamation point to see the error information provided to assist with troubleshooting the connection issue.
Save the Model Server to retain the configuration.
-
Change the name of the model server.
-
Add a description to identify the model server when Data Hub contains multiple model servers or if the model server will be shared with other users.
Warehouse connection
-
Environment Type
From the drop-down list, select one of the following:
- On-premises SQL Server - Select this option to connect to a Microsoft SQL Server.
- Azure SQL Server - Select this option to connect to an Azure SQL Server.
- Provisioned SQL Server - Select this option to connect to a Provisioned SQL Server.
-
Server
-
On-premises SQL Server - Enter the server name if the server hosting the Microsoft SQL Server Database Engine instance is on the same network as the Data Hub. Otherwise, a fully-qualified domain name (FQDN) is required.
-
Azure SQL Server - Enter the SQL server URL provided by Microsoft Azure.
-
-
Login Type
Specify one of the following authentication settings:
-
Application pool: Allows Data Hub to connect to the Warehouse using an application pool service account (if configured). For information on application pools, see: About Authentication in a Model Server or Adjust Executing User for Application Pool. (On-premises SQL Server only)
-
Specified: Allows Data Hub to connect to the Warehouse using a Windows user name (including domain) and password. (On-premises SQL Server only)
-
SQL Server login: Allows Data Hub to connect to the Warehouse using SQL Server user name (including domain) and password.
Warehouse options
Bulk-copy batch size sets the number of rows to use per transaction when performing bulk copy operations. The default setting is 200000.
A value of zero indicates that the operation will be executed as a single transaction. This option is fast but requires temporary storage (within the tempdb table) for all of the rows imported from a source until the transaction is committed. You may need to specify a non-zero value when working with large source tables.
About authentication in a model server
Because of the way SQL Server Analysis Services (SSAS) works, selecting the Application pool as the authentication credentials for the warehouse connection specifies two sets of credentials for connecting to the data warehouse SQL Server database. This fact can produce unexpected errors when processing the cube.
The data warehouse must be accessed by two entities: Data Hub and SSAS. The process works as follows:
-
Data Hub creates the data warehouse with tables and views configured the same as the model and its pipelines. (Data Hub creates the cube and adds relevant structure information.)
-
When the Process button is clicked, Data Hub firstly reads data from the source system's database into the data warehouse.
-
Once the data warehouse is populated, Data Hub calls SSAS to process the cube. SSAS then reads and processes the information in the data warehouse and writes it into the cube.
In steps 1 and 2 above, Data Hub is accessing the data warehouse. In step 3, SSAS is accessing the data warehouse directly. When a specific user's credentials are specified, they are used for all three steps, as they can be passed to SSAS for its use in step 3.
However, if the Application pool is specified for the warehouse connection, the first two steps are executed using the Internet Information Services (IIS) Application pool's user, which is usually the service identity (user) specified for IIS (although any user may be specified).
Security restrictions prevent the application pool's user credentials from being passed to SSAS, so the third step is executed by SSAS using its service identity, not the Application pool's user. These users may well be different. If they are, the situation can arise where the first two steps complete normally, but the third one fails because the SSAS service identity has insufficient permissions to access the data warehouse.
Clicking Test Connection on the model server screen will test both sets of credentials and display an appropriate error if either set cannot access the data warehouse. Zap recommends you do this whenever you specify the Application pool for the warehouse connection.
If you see an "Insufficient access to process cubes" error, the SSAS service identity has insufficient permission to access the data warehouse.
The solution to this error is to either:
-
Use a specific user's credentials for the warehouse connection, and make sure the user has sufficient SQL Server permissions to access the data warehouse. OR
-
Grant the SSAS service identity sufficient SQL Server permissions to access the data warehouse.
The service identity that SSAS runs under may be one of the predefined service identities listed below or a domain user. You can view the service identity that an instance of SSAS uses by examining the Log On As property for its entry in SQL Server Configuration Manager. The three predefined identities are listed below:
-
NT AUTHORITY\NETWORK SERVICE
-
NT AUTHORITY\LOCAL SERVICE
-
NT AUTHORITY\SYSTEM (may appear as "LocalSystem")
These predefined identities are local to each server. The above names appear when viewing these identities in the context of their local server. However, when working with predefined identities from a server in the wider domain context, the identities appear as DomainName\ServerName$ to avoid confusion with the other servers' local versions of the identities.