How to: Connect Excel to the Comsense Data Warehouse.

Environment: Excel 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

In the About Comsense Advantage window:

3. Note the SQL server instance name

4. 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

3. Click From Other Sources
4. Click From SQL Server

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

2. Leave Log on credentials section as Use Windows Authentication

3. Click Next

Select Database and Table Page

In the drop-down menu:

1. Select ComsenseDataWarehouse

If you require multiple tables:

2. Check the Enable selection of multiple tables checkbox.

In the Data Table pane:

3. Select the appropriate table(s)

4. Click Next

Save Data Connection File and Finish Page

In the Data Connection Wizard:

1. Click Finish

In the Data Import window:

2. Select the data output

3. 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

In the Excel Options window:

3. Click Add-ins

In the Manage drop-down list:

4. Select COM Add-ins

5. Click Go...
   • The COM Add-ins window will open
6. Check the Microsoft Power Pivot for Excel checkbox

7. Click OK

PowerPivot is now enabled.

Accessing PowerPivot's Table Import Wizard

On the blank Excel sheet:

1. Click the PowerPivot tab

2. Click Manage
   • The Power Pivot for Excel workbook will open

In the Power Pivot Workbook:

3. Click From Database

4. Click 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

2. Leave Log on credentials section as Use Windows Authentication if your server is On-Premise
3. On-Premises Server: leave the log-on credentials section as "Use Windows Authentication".
4. Comsense Cloud; change the credential section to "Use SQL Server Authentication".
5. Comsense Cloud credentials are only provided to the Primary Contact.

In the Database name drop-down list:

3. Select ComsenseDataWarehouse

4. 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.

Select Tables and Views

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

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

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.

Moving Data from PowerPivot to Excel

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

Comsense Data Warehouse Data Diagrams

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