Connecting Excel 2013 to the Comsense Data Warehouse Database

Excel 2013 includes features for analyzing data using the traditional direct connection method, plus since version 2010 the addition of the PowerPivot and PowerQuery features provide new advanced interactive connection methods as well. The key to using any tool to utilize the Comsense Data Warehouse is simply knowing your SQL Server instance name. With that in hand many tools can be used to work with your data.

Configure Active Directory Group for ComsenseDWUsers

The Comsense Data Warehouse has been configured to combine the security and ease of use of Windows Authentication with the intelligence of your Comsense Opening Suite configuration. When deployed, the Comsense Data Warehouse was configured to use an Active Directory security group usually named ComsenseDWUsers. Once configured your organization simply needs to add and remove Windows Active Directory accounts to and from this security group in order to provide access to the data warehouse database.

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

As a member of the ComsenseDWUsers security group you can use Excel and any other data analysis tools to work with your data.

Making Connections using the Traditional Direct Method

  1. Open a new Excel worksheet.
  2. Select the worksheet and cell where you want the retrieved data table to return.
  3. On the Data ribbon click From Other Sources > From SQL Server

    Excel sheet; shows the pathway to From SQL Server.
  4. On the Data Connection Wizard dialog, enter your SQL Server Instance Name.

    Data Connection Wizard window; shows an example SQL server name.
  5. If you are unsure of your server name, view the Help > About Comsense Advantage window in Comsense Opening Suite. Your SQL Server name is listed as SQL Server Instance.

    About Comsense Advantage window; shows the SQL Server Instance.
  6. Leave the Log on Credentials set to Windows Authentication. Click Next.
  7. On the Select Database and Table step, choose ComsenseDataWarehouse.
  8. Select the table, or tables you wish to retrieve. To retrieve multiple tables, check the option to Enable selection of multiple tables. Click Next.

    Data Connection Wizard; shows the ComsenseDataWarehouse database selected and a selected table.
  9. Click Finish to save the connection information and retrieve the data.

    Data Connection Wizard; shows the File Name and the Friendly Name.
  10. You will be prompted with options for what you want to do with the retrieved data. Choose the option that suits your scenario.

    Import Data window; shows PivotTable Report and Existing worksheet selected.

 

Using PowerPivot to Retrieve Data from the Comsense Data Warehouse

  1. Office 2013 Professional Plus or Excel 2013 standalone editions are required for PowerPivot.
  2. Open a new Excel worksheet.
  3. To enable the PowerPoint add-in if not already done, click File > Options > Add-Ins > on the bottom dropdown select COM Add-ins > enable Microsoft Office PowerPivot for Excel 2013.
  4. On the PowerPivot ribbon click Manage.
  5. In the Get External Data ribbon group click From Database > From SQL Server

    PowerPivot Excel window; shows the Get External Data ribbon and the pathway to From SQL server.
  6. On the Table Import Wizard you can type your server name if known, or click the drop down to have it find available SQL Servers on your network.
  7. Leave Use Windows Authentication selected.
  8. For Database Name choose ComsenseDataWarehouse.

    Table Import Wizard; shows ComsenseDataWarehouse in the Database name field.
  9. Click Next.
  10. You can now choose to either select from existing tables and views or write a query. We’ll choose Select an existing table for this example. Click Next.
  11. Select the tables you wish to include in the data model. Click Next.

    Table Import Wizard; shows selected source tables.
  12. A summary page will indicate progress. Click Close to complete.
  13. Each table will be returned to the PowerPivot window in its own tab.
  14. Use the Diagram view to define relationships between tables to enable pivot tables to relate information properly.
  15. To push the data from the PowerPivot model over to Excel, click the PivotTable dropdown and choose an option that suits your scenario. Options include PivotTable, PivotChart, and combinations.

    PowerPivot Excel window; shows the PivotTable drop-down menu.
  16. The example below was created using the Chart and Table (Horizontal) option.

    Excel window; shows an example of the Chart and Table (Horizontal) option from PowerPivot.
  17. PowerPivot is a very powerful and far-reaching feature in Excel 2013. To begin learning more visit the PowerPivot page on Office.com at https://support.office.com/en-US/Article/everything-else-you-can-do-in-Power-Pivot-d7b119ed-1b3b-4f23-b634-445ab141b59b.

Comsense Data Warehouse Data Diagrams

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

 

SQL Server 2008 Native Client

In some cases the Microsoft SQL Server 2008 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 - http://download.microsoft.com/download/0/D/2/0D2D9A1F-1461-46DC-9CB9-62A43B26B672/ENU/x86/sqlncli.msi

x64 – sqlncli.msi - http://download.microsoft.com/download/0/D/2/0D2D9A1F-1461-46DC-9CB9-62A43B26B672/ENU/x64/sqlncli.msi

Have more questions? Submit a request

Comments