Getting started with the sample BI Excel report packages

Contact Expert v6.1 for Skype for Business Server

Connecting the sample Excel file to the CE database

Based on information within the BI documentation, one can build sophisticated reports in Excel Power Pivot starting with an empty Excel file. However sample Excel report packages for the current CE version can be downloaded from the knowledge base. This chapter explains how these sample Excel report packages can be connected to a live Contact Expert database.

Follow the next steps to connect the sample Excel Power Pivot file to an actual CE database. This procedure should be done only once as Excel will store connection information

1. BI Reporting Prerequisites#EnablingPP in Excel if it is not available.

2. BI Reporting Prerequisites#InstallingSQLNativeClient if necessary. This driver is not required for PCs where users will use the reports, but will not refresh the data from the CE database.

3. Download the up-to-date sample report package from page Sample Power Pivot Report Packages and unzip its content into an appropriate folder.

4. Launch Excel with one of the sample BI report packages. Depending on the security settings in your operating system, you might receive warning messages. Press the button "Enabling Editing" and "Enable Content" if asked.

5. Select Power Pivot menu tab and press Manage (leftmost button). This opens the administration window of the powerful in-memory tabular Power Pivot database engine where you can define the data model with tables from different sources, calculated columns, complex measures, KPIs, relations etc. Each tab contains one data table. For the sample BI report package files the data model is prepared, but most of the tables are originally empty.

6. The main menu probably already shows the Home bar, but if not, in the Power Pivot File / Home / Design main menu select Home.

7. Click on Existing Connections button to open the connections dialog.

8. Select Contact Expert connection in the PowerPivot Data Connections section.

9. Press Edit to set the live Contact Expert connection information.

10. Define the live CE SQL server address in the Server name field. You might leave the user name as CEPersonalBI which is the reporting user defined by the CE upgrade/install script. If your database administrator had defined another name, enter that name.

    Do not check the "Save my password" check box! There is a known Excel Power Pivot bug that makes connection to the database impossible with a save password. A fix is expected to arrive in Summer 2016 from Microsoft. Until that report users should enter the password each time they download fresh data from the CE database. In fact due to security reasons it is not recommended to save database passwords with the file.

11. Define ACEReport in the Database name field. You might press Test connection to see if server name, user and password are OK or not.

12. The sample files are configured to be used with Contact Expert databases running on MS SQL Server 2010 or later. If the Contact Expert database is on MS SQL Server 2008 the data provider defined in the connection settings should be changed from "SQL Server Native Client 11.0" to "SQL Server Native Client 10.0" (and the BI Reporting Prerequisites#InstallingSQLNativeClient on the PC). TO change this setting, press the Advanced button and in the new dialog box change the provider.

13. Click Save to modify the settings or Cancel to leave as it was.

14. Click Close to close the Connection dialog. Don't press Refresh at this point. In theory it should work, but it is safer to load the data from Excel (and not from Power Pivot). Most of the users will do that anyhow and they will possibly never open the Power Pivot window. For them Power Pivot database manager will work invisibly at the background.

15. Press Save in the File menu or on the Quick Access Toolbar.

16. Close Power Pivot window (it will run in the background until you quit from Excel) and switch back to the Excel window.

17. Save the file and exit.

At this point the data is not yet loaded to the Excel file, this is still empty. It is a convenient format to distribute it to the report users. Before distribution, let's test it using the procedure the users will use (see next chapter).

Downloading fresh data from the CE database to the Excel file

Normal report users will use standard Excel functionality to download data from the Contact Expert database.

1. Launch the file in Excel.

2. Select the Data menu.

3. Press the Refresh All... button.

4. Enter username and password if requested (CEPersonalBI / 1234 by default).

5. The Excel status bar at the bottom of the screen should display data download progress.

Several million data records might be downloaded. If you experience performance problems, restrict the data to be downloaded using the method described in the next chapter.

The current version of Power Pivot sometimes fails to synchronize the modified connection information with Excel. If your connection test was successful in Power Pivot when you had defined the connection, but Excel fails to download the data, then go back to the downloaded package, connect to CE again following the procedure described in the previous paragraph very carefully.

Filtering data to be loaded to the Excel Power Pivot data model

If your Contact Expert database contains several years of data, you might want to filter the data when loading it from the database to the Excel Power Pivot data model. Unfortunately Excel does not provide a simple method to use adjustable parameters in the connection information. In subsequent releases we will provide information about how to filter information using VBA scripting. As script enabled Excel files (XLSM) are not allowed in many environments, in this release we recommend a less flexible, but safer method. This configuration should be performed by the database administrator and will not be configurable at each data refresh.

There is a database table in ACEReport database: [ACEReport][bi][ParamsTable] containing some global configuration parameters for BI reporting. There is a record in the table with the following information:

Param	Value	Description
FromDate	1990-01-01	Reads data records only after this date.

This parameter defines from which date (until today) the data should be downloaded. Set this to an appropriate date in YYYY-MM-DD format and save the table. Now all data retrievals from Excel using the BI database views will see data only from that start date.