Digizuite™ Insights 2.0.0 - Installation

Owned by Troels Holm (Unlicensed)
Last updated: Jun 12, 2019 by Norman Cummins (Unlicensed)

Requirements

  • Microsoft™ SQL Server 2014 or higher with an existing DAM installation.
  • To use the Power BI reporting service, at least one Power BI PRO account with configured on-premises data gateway.
  • Digizuite™ Insights files: Database installer and the Power BI file.


When creating SQL logins (or using integrated) it is important that the user only has read access (db_datareader) to the DAM database.


Inside the PBIX file there is a stored data source.
It is important that this data source (SQL server and credentials) is the same as the one used in the Power BI data gateway.
The easiest way to achieve this, is to have the Power BI Desktop and the Power BI Gateway installed on the same machine (the SQL server).

Database installation / update

The database content is delivered as an executable that performs all necessary updates an existing Insights database.
Step 1 and 2 are only necessary for new installations. If performing an update you may skip to step 3.

  1. Create a new Insights database (e.g. "dzreporting") next to the DAM database.
    Note: Make sure the Insights database has the same collation as the DAM database.

  2. Create a new SQL server login (e.g. "insights").
    You can use any authentication you want (Windows/SQL), but it should have the following properties:
    1. Default database = "dzreporting" (database created in 1). Be sure to uncheck the "Enforce Password Policy, and select the DZreporting database under Default Database (changing it from master to DZreporting).
    2. Under User Mapping set  "db_owner, public" for the insights database, and "db_datareader" for the DAM database.

  3. Unblock and unpack the database zip file into a local folder.

  4. Edit the file "Digizuite.Insights.DbUpgrade.exe.config" with a text-editor and change to match your setup:
    1. ConnectionString (use credentials created in step 2). Remember to set the Initial Catalog = dzreporting.
    2. DAMDatabaseName (to be typed in under "value= ")
    3. (Best pratice) Remember to backup the database i.e. yourdatabasename_dam before prior to step 5.
  5. Run the executable "Digizuite.Insights.DbUpgrade.exe" and inspect the output (optionally using the created 'log.txt').

Power BI 

The visualizations are delivered in a PBIX file that contains all new features and fixes.
Steps 1-5 are only necessary for new installations. If performing an update you may skip to step 6. 

  1. Create a Power BI PRO account or get an existing.

  2. Download and install the Power BI gateway on any computer (recommended is the SQL server).
    https://powerbi.microsoft.com/en-us/gateway/

  3. Setup the Gateway using the Power BI account and ensure that is is available in Power BI settings afterwards.
    The credentials must define "db_datareader" in both the Insights and DAM database.
    The credentials may be restricted to the SELFSERVICE schema in the Insigths database..
    You must use the same data source settings as used in step 6.

  4. Go to the DataSet in Power BI and enable desired schedule for updating the online cube.
    While it vary by installation and data, it is recommended to schedule updates 2 times a day maximum.
    If the schedule requires more frequent updates, it is recommended to setup the Insights solution with a datawarehouse. 

  5. Install Power BI Desktop on any computer (recommended is the SQL server).
    https://powerbi.microsoft.com/en-us/desktop/

  6. Open the PBIX file and ensure that the data source is correct for the Insights database:
    See: File→Options and Settings→Data source settings
    The credentials must define "db_datareader" in both the Insights and DAM database.
    The credentials may be restricted to the SELFSERVICE schema in the Insigths database..
    You must use the same data source settings as used in step 3.

  7. Publish to Power BI.


You may also want to use the Power BI mobile app (https://powerbi.microsoft.com/en-us/mobile/) which is available both in Apple App Store and Google Play.

Self-service access 

To setup self-service access you need to create a SQL login that defines:

  • Read access to the Insights database using the SELFSERVICE schema.
  • Read access to the DAM database.
  • Execute rights on the [dbo].[spHelp] stored procedure.


When using the SELFSERVICE access, caution should be taken about usage. In this scenario, you are typically not controlling the usage and update schdules.
This could potentially lead to overloaded usage and locking. If you are not controlling these schedules, it is recommended to setup the Insights solution with a datawarehouse


Client registrations

Analytics data needs to be sent to the API in order to show in Digizuite(tm) Insights. Applications that send data are considered to be Insights clients. Some clients always send these informations to the API while others needs to be configured for it:

  • DAM Center is always registered to send analytics data.
  • Media Manager needs to opt-in by setting the app setting "EnableDAMStats" to "true".


Insights Installation & Configuration Guides

On the SQL Server, right click Databases, and select New Database

In the New Database dialogue box type a name for the Database EG DZReporting and click OK

Select Options and ensure the DZReporting Database is using the same Colation as the DAM Database.

This can be configured from Options in the left hand pane.

On the SQL Server expand Databases and check the Database has been created

Expand Security and right click Logins and click New Login

In the Login New dialogue box type a name for the new Login EG (Insights)

Type a password for the login

Deselect the 3 password policy check boxes

In the Default database drop down menu select DZReporting


In the Select a page panel, select User Mapping, in the Users mapped to login select DZReporting and below in the Database role membership for : DZReporting box select db_Owner.

Then select the Dam database in the Users mapped to login and below in the Database role membership for : the dam database select db_datareader and click OK


Copy the Insights installation zip folder to the Server, unblock and extract the files.

Open the extracted folder and edit the config file with Notepad or any other Text editor.

In the XML file the Connection string and database name need to be input and the changes saved

Next we run the Digizuite.Insights.DbUpgrade by double clicking the exe file

Click Yes in the User Account Control dialogue box.

The upgrade process starts to run

This may take some time, as the database is 80mb in size

After the upgrade is complete the next step is to import the PBIX file into Power BI, the simplest way is to double click the PBIX file, or from Power BI Desktop click File, Open and browse to the file location 

and click Open

  

This will load the PBIX in to Power BI Desktop

This has loaded the template as is, it now needs to be configured to Point to the right Database and login with the right credentials

Click File, Options and Settings and Data source settings

In the Data source settings dialogue box, right click the current source and select Change Source

In the SQL Server database type the Servers IP address and the database name and click OK

Right click the source again and select Edit Permissions

In the Edit Permissions dialogue box Under Credentials click Edit

In the dialogue box, select Database in the left hand panel, then enter the Username and password for the login you created earlier on the SQL Server, then click Save.

Click OK and click Close

In Power BI Click Refresh to update the data loaded in the PBIX file/template


Your dataset is now loaded, the next step is to Publish this to the Power BI Service

From the ribbon in Power BI Desktop click Publish, you will be prompted Do you want to save your changes ?

Click Save



You will be prompted to Select a destination to Publish to, make your choice and click Select.

The process will now run

Once completed your are presented with the following dialogue box


The next step is to configure and test the gateway.

From the Power BI Service/Portal select downloads, then data gateway

Click on the Download Gateway button

After the download has completed, browse to thefiles location, right click the application and select Run as administrator

Click Next in the On premises data gateway installer dialogue box

After choosing On-premises data gateway (recommended) option, click Next

The installer will start preparing the installation files

Click Next in the Reminder befor you install dialogue box

In the next dialogue box, choose an install location or accept the default, click to accept the terms of use and privacy statement, then click Install

The installation will now begin

After the installation is completed you will be prompted to enter an email address to be used with the gateway, then click sign in

Enter your login credentials in the EnterpriseGatewayConfigurator box and click OK

Next you will need to register the gateway, assign the gateway a name, input a recovery key, add to an existing gateway cluster if one exists and click Configure.


After the configuration is completed you will see the follwoing dialogue box, click Close

To configure the gateway, click the settings cog in the Power Bi Service portal, then click Manage gateways

On the Gateway Cluster Settings page, select your gateway and click Add data source to use the gateway

In the Data Source Settings complete the Data Source Name, Select the Data Source Type, Server IP Address, Database Name and Select an authentication method and click Add

Once the connection has been setup the following dialogue box will be displayed

The gateway is now ready for use.


Tip


If the connection fails it could be because the PowerBI Gateway Service has not started, to start the service.

Open Command Prompt as Administrator and run the following commands:

net stop PBIEgwService

net start PBIEgwService

Once the service is running your Gateway Connection should succeed.