Let’s say you’re a senior BI developer responsible for several Analysis Services Tabular instances (Dev, Test, Prod), each of which may host multiple databases. Each database, moreover, may have multiple database roles and may also contain multiple perspectives. Within the course of a single day (or certainly a week) it may be necessary to access several of these specific contexts of the BI environment as part of ongoing projects or routine maintenance and administrative responsibilities.
To increase productivity and reduce the risk of errors from continually configuring custom database connections manually, it may make sense to create and centrally store connection files and connection string parameters within a Visual Studio solution which already contains an Analysis Services project and potentially related BI projects (Reporting Services, Integration Services).
Use Cases for Persisted Connection Strings
I assume many readers are familiar with the relationship of database roles and perspectives to security and usability for Tabular models, respectively. As one example, the BI developer may be tasked with implementing object-level security for an Analysis Services 1400 compatibility level model for one or multiple of the database roles and thus would need to test/query the database as a member of the given role(s) to ensure the data and metadata is secured as intended.
As another example, the BI developer may wish to quickly view and explore a Tabular model from a client tool like Excel in the context of a particular perspective. The intent could be to evaluate the user experience and options for improvement such as alternative display folder structures or to analyze the data itself as part of a troubleshooting effort or to support further design/dev plans.
Custom Office Data Connection (ODC) Files
The Analyze in Excel feature in SQL Server Data Tools (SSDT) is useful but in many cases you’ll need to connect to the actual deployment server, not the workspace server or the integrated workspace (which is recommended). Additionally, once the custom ODC files are created, you don’t even have to open the solution in Visual Studio – you can open an Excel workbook with the specific connection context (Perspective, Security Role) directly from Windows file explorer.
Steps to Create
- Connect to the Analysis Services instance and tabular database from Excel
- In Excel 2016+ this is via the Data tab, Get Data, From Database, From Analysis Services Database
- Specify the server and database in the Data Connection Wizard
- Click ‘Finish’ on the Save Data Connection File and Finish form of the wizard
- Click OK for a PivotTable Report – you should now have the fields list on the right
- On the Data tab in Excel, click Properties to open the Connection Properties window
- If the Properties icon is grayed out, click inside the pivot table
- Select the definition tab of the Connection Properties form to view the connection file, string, and Export Connection File command per the following image:
Export Office Data Connection File
- Click Export Connection file
- Save the ODC file in a directory path with the BI solution files
- A dedicated folder for multiple ODC files probably makes sense
- Add the ODC file to the BI solution in Visual Studio
ODC File in Visual Studio Solution - Open the ODC file in Visual Studio to specify the connection parameters required:
Customize ODC File Properties - If necessary modify values for connection string properties such as the following:
- Data Source: Analysis Services Server instance
- Initial Catalog: Analysis Services Database
- Roles: Database Role(s)
- You need to insert the Roles property and its value for at least your first custom ODC file.
- CommandText: Perspective
- The value will be Model if no perspective is specified
- Rename the ODC file and make copies of the ODC file for the given number of custom connections you’ll want/need access.
- Customize and save these additional ODC files within the solution
In the example above, custom ODC files reflect a single database (AdWorksImport) on one server (ATLAS) with three database roles and three perspectives. Of course, you may only need a few ODC files for the most common or important servers and user contexts. Depending on the volume of files, you may further organize the connections into subfolders based on server and/or database.
When you’re working in an enterprise environment with multiple servers and large, complex models with security roles and perspectives this relatively small investment of time to create custom ODC files can pay off significantly over time.
Connection Parameter Strings for SSMS
The ODC files are good for the client/user experience but many BI developers will prefer to test out role-based security via their own custom DAX expressions in SQL Server Management Studio (SSMS). To accomplish this, click ‘Options’ from the Connect to Server dialog and then specify parameter values for various properties on the Additional Connection Parameters page per the following two images:
The following documentation on Analysis Services connection string properties provides rich details and examples. For example you may pass in an EffectiveUserName value in a domain\user format.
Note: Power BI Datasets, which are Analysis Services databases, will be a supported source for paginated reports at some point in the relatively near future. Therefore, some knowledge of DAX as a query language could be leveraged to develop ‘pixel perfect’ operational reports while Power BI and other tools build interactive reports against the dataset.
Again, given the volume of databases and security roles across the environment, it may make sense to save these common connection string parameters. For this, you could just add a simple text file to the solution in Visual Studio with a commented list of string properties such as the following:
// 1. AdWorks Import Database (All security roles by default) Initial Catalog=AdWorksImport // 2. AdWorks Import Database, Call Center Security Role Initial Catalog=AdWorksImport; Roles=CallCenterOnly // 3. AdWorks Import Database, Online Sales Role Initial Catalog=AdWorksImport; Roles=InternetSalesOnly // 4. AdWorks Import Database, Reseller Sales Role Initial Catalog=AdWorksImport; Roles=ResellerSalesOnly
Specifying a value for the initial catalog property avoids the common issue of querying the wrong database and needing to change the database connection.
In a scenario in which the BI developer needed to test security by impersonating one of the database roles, she could simply copy from the text file in Visual Studio and paste into the dialog in SSMS. While the various objects in the model and the security requirements may change significantly over time, it’s unlikely that the names of the security roles will change.
Other Tools for Testing
You could definitely use DAX Studio rather than SSMS for database role testing. In my view, however, this isn’t currently a strength of DAX Studio and SSMS provides access to other administrative functions which may be relevant for testing scenarios.
Rather than ODC files for Excel, you could create multiple Power BI Desktop files connected to the various servers and perspectives. However, it’s only via the ODC files that you can impersonate a database role for security testing so this gives the edge to Excel/ODC for now.
A broader topic that could make for a future blog post is the future of SQL Server Data Tools. For example, we know that enterprise modeling features will be built into Power BI Desktop and it’s certainly possible that a new or revised tool will be used for paginated reports targeting Power BI.
Wrapping Up
Saving and centralizing customized connection strings and files, while not ‘game changing’ by any means, can benefit productivity as well as minimize the tedious nature of manually re-configuring Analysis Services data connections. This approach provides the BI developer with quick and reliable access to specific contexts of Analysis Services models from across a BI environment. (On a related note, you may consider my Tabular Schema Reference file to quickly reference and analyze the structure of a Tabular model.)
If you find blog posts like this you can click ‘Follow’ to get notified of future posts. Additionally, as always feel welcome to leave comments regarding this post or Insight Quest generally.
Bonus: Boston BI 3000
I wasn’t able to attend this weeks’ Boston BI User Group meetup but the following report analyzes membership data (provided by Meetup.com) and group events since last year:
Insight Quest 