Connecting Excel 2013 to the Comsense Data Warehouse Database

How to: Connect Excel to the Comsense Data Warehouse.

Environment: Excel 2013 & 2016

In this article:

About Excel & PowerPivot

Excel has features that analyze data using the traditional direct connection method. Excel versions 2010 or later have the option of PowerPivot and PowerQuery. PowerPivot and PowerQuery provide new advanced interactive connection methods. To utilize the Comsense Data Warehouse for both methods you need your SQL Server instance name. Using the SQL Server instance name you can access your data and use the tools in Excel and PowerPivot to analyze your data. 

Obtaining Your SQL Server Instance Name

In Advantage:

  1. Click Help
  2. Click About Comsense Advantage
Click image to enlarge
Comsense Advantage; shows the location of the Help menu and About Comsense Advantage.
Image 1: Help Drop-Down Menu

In the About Comsense Advantage window:

  1. Note the SQL server instance name
Click image to enlarge
About Comsense Advantage window; shows the location of the SQL Server Instance number.
Image 2: SQL Server Instance
  1. Click Close

You now have your SQL server instance name.

Configuring Active Directory Group for ComsenseDWUsers

The Comsense Data Warehouse combines the security and ease of Windows Authentication with the intelligence of your Comsense Opening Suite configuration. The Comsense Data Warehouse uses an Active Directory security group usually named ComsenseDWUsers. Once configured, you can add and remove Windows Active Directory accounts to and from the security group to provide access to the data warehouse database.

By combining your Windows Authentication login with your Comsense Opening Suite login, the data warehouse only provides data for authorized divisions in Comsense Opening Suite.

Members of ComsenseDWUsers security group can use Excel and any other data analysis tools.

Connecting Excel to the Data Warehouse Using the Traditional Direct Method

Only use the traditional direct method to create a custom report and pivot table without PowerPivot. In most cases, we recommend using PowerPivot because the data warehouse contains a substantial amount of data.

*Note: The procedure below uses Excel 2016, other versions of excel may be slightly different but follow a similar procedure.

Accessing the Data Connection Wizard

In a new Excel worksheet:

  1. Select a data cell
    • The data will populate here
  2. Click Data
Click image to enlarge
Excel Workbook; shows the location of the Data tab.
Image 3: Data Tab
  1. Click From Other Sources
  2. Click From SQL Server
Click image to enlarge
Excel Workbook; shows the From Other Sources drop-down menu and the location of From SQL Server.
Image 4: From Other Sources Drop-Down Menu

The Data Connection Wizard will open.

Completing the Data Connection Wizard

Connect to Database Server Page

In the Data Connection Wizard:

  1. Enter the SQL server instance name in the Server name field
Click image to enlarge
Data Connection Wizard, Connect to Database Server page; shows the Server name field with the server name entered.
Image 5: Server Name Field
  1. Leave Log on credentials section as Use Windows Authentication
Click image to enlarge
Data Connection Wizard, Connect to Database Server page; shows Use Windows Authentication selected.
Image 6: Log on Credentials
  1. Click Next

Select Database and Table Page

In the drop-down menu:

  1. Select ComsenseDataWarehouse
Click image to enlarge
Data Connection Wizard, Select Database and Table page; shows the Database drop-down list and the location of ComsenseDataWarehouse.
Image 7: Database Drop-Down List

If you require multiple tables:

  1. Check the Enable selection of multiple tables checkbox.
Click image to enlarge
Data Connection Wizard, Select Database and Table page; shows the location of the Enable selection of multiple tables checkbox.
Image 8: Enable Selection of Multiple Tables Checkbox

In the Data Table pane:

  1. Select the appropriate table(s)
Click image to enlarge
Data Connection Wizard, Select Database and Table page; shows two tables selected.
Image 9: Selected Tables
  1. Click Next

Save Data Connection File and Finish Page

In the Data Connection Wizard:

  1. Click Finish
Click image to enlarge
Data Connection Wizard, Save Data Connection File and Finish page; shows the location of the Finish button.
Image 10: Finish Data Connection

In the Data Import window:

  1. Select the data output
Click image to enlarge
Import Data window; shows the PivotTable Report selected.
Image 11: Import Data Window
  1. Click OK

Excel will populate with the data.

Connecting PowerPivot to the Data Warehouse

PowerPivot is a powerful Excel tool. The key differences between a standard pivot table and PowerPivot are that PowerPivot:

  • Has no row limit and can handle more data
  • High-speed performance with large data
  • Allows custom date grouping
  • Can combine data from multiple sources

For more detailed information on the capabilities of PowerPivot see this Microsoft article on PowerPivot.

Enabling PowerPivot on Excel

PowerPivot is available for free with standalone Excel 2013 & 2016 or Office Professional Plus 2013 & 2016 (2016 via volume licensing). For more information on how to access PowerPivot see this Microsft article on where to find PowerPivot.

In a new Excel worksheet:

  1. Click File
  2. Click Options
Click image to enlarge
Excel Workbook, File menu; Shows the location of Options.
Image 12: Excel Options

In the Excel Options window:

  1. Click Add-ins
Click image to enlarge
Excel Options window; shows the location of the Add-ins.
Image 13: Excel Options Window

In the Manage drop-down list:

  1. Select COM Add-ins
Click image to enlarge
Excel Options window, Add-ins tab; shows the Manage drop-down list and the location of COM Add-ins.
Image 14: Manage Drop-Down List
  1. Click Go...
    • The COM Add-ins window will open
  2. Check the Microsoft Power Pivot for Excel checkbox
Click image to enlarge
COM Add-ins window shows the Microsoft Power Pivot for Excel checkbox.
Image 15: COM Add-ins Window
  1. Click OK

PowerPivot is now enabled.

Accessing PowerPivot's Table Import Wizard

On the blank Excel sheet:

  1. Click the PowerPivot tab
Click image to enlarge
Excel Workbook; shows the location of the Power Pivot tab.
Image 16: Power Pivot Tab
  1. Click Manage
    • The Power Pivot for Excel workbook will open
Click image to enlarge
Excel Workbook, Power Pivot tab; shows the location of the Manage button.
Image 17: Manage Button

In the Power Pivot Workbook:

  1. Click From Database
Click image to enlarge
Power Pivot for Excel workbook; shows the location of From Database.
Image 18: From Database
  1. Click From SQL Server
Click image to enlarge
Power Pivot for Excel workbook; shows the From Database drop-down menu and the location of From SQL Server.
Image 19: From SQL Server

The Table Import Wizard will open.

Completing the Table Import Wizard

Connect to a Microsoft SQL Server Database Page

In the Table Import Wizard:

  1. Enter the server instance name or Select the server name from the drop-down list
Click image to enlarge
Table Import Wizard, Connect to a Microsoft SQL Server Database page; shows the Server Instance in the Server name field.
Image 20: Server Name Field
  1. Leave Log on credentials section as Use Windows Authentication

In the Database name drop-down list:

  1. Select ComsenseDataWarehouse
Click image to enlarge
Table Import Wizard, Connect to a Microsoft SQL Server Database page; shows ComsenseDataWarehouse in the Database name field.
Image 21: Database Name Field
  1. Click Next

Choose How to Import the Data Page

In the Table Import Wizard, Select Select from a list of tables and views to choose the data to import and Click Next.

Click image to enlarge
Table Import Wizard, Choose How to Import the Data page; shows Select from a list of tables and views to choose the data to import is selected.
Image 22: Data Import Method

Select Tables and Views

In the Table Import Wizard, Select the tables for your data model and Click Finish.

Click image to enlarge
Table Import Wizard, Select Tables and Views page; shows two tables selected and the location of the Finish button.
Image 23: Selected Tables

The Table Import Wizard will process the request and display a summary page of data import. Click Close.

Click image to enlarge
Table Import Wizard; shows the Success table import status and the location of the close button.
Image 24: Table Import Status

Using PowerPivot

Excel creates a tab for each table selected in the wizard. Click on the tabs to view table information.

Creating Relationships Between Tables

To create relationships between the different tables, Click Diagram View.

Click image to enlarge
Power Pivot for Excel workbook; shows the location of Diagram View.
Image 25: Diagram View
Click image to enlarge
Power Pivot for Excel workbook; shows the Estimate table right-click menu and the location of Create Relationship...
Image 26: Create Relationship

Moving Data from PowerPivot to Excel

To move the data from PowerPivot to Excel, Click PivotTable and Select your preferred output.

Click image to enlarge
Power Pivot for Excel workbook; shows the PivotTable drop-down menu.
Image 27: PivotTable Drop-Down Menu
Click image to enlarge
Excel Workbook; shows and example of the data expressed in a pivot table.
Image 28: Excel Pivot Table Example

Comsense Data Warehouse Data Diagrams

Visit our online help to view some illustrations of the data relationships in the Comsense Data Warehouse

SQL Server 2012 Native Client

In some cases the Microsoft SQL Server 2012 Native Client must be installed to provide data connectivity from Excel and other tools to the server. Note that even on 64-bit installations of Windows the x86 version of the SQL Native client is required for use with 32-bit versions of Excel, which is currently most common.

x86 – sqlncli.msi - https://comsensedownloads.blob.core.windows.net/customerdownloads/Installers/sqlncli11x86.msi

x64 – sqlncli.msi - https://comsensedownloads.blob.core.windows.net/customerdownloads/Installers/sqlncli11x64.msi

Have more questions? Submit a request

Comments