BI4Dynamics Application - Installation Manual for BC & NAV

1. Installation #

Installation documentation consists of documents:

Installation of BI server on Local Machine or Azure Virtual Machine (VM)
Installation of BI4Dynamics application – this process is covered by this document
There is very little difference in application installation on Local or VM.

1.1. Prerequisite #

All prerequisite fields are result of that Local or VM installation (fields here are for example only):

Permissions

All six (6) accounts can be combined into one (1) Windows domain account:

Be (local) administrator on the windows server where BI4Dynamics is installed.
Read permissions on the BC database.
Read permissions on the master database of the source database SQL Server (only required if the BC license is uploaded to the SQL server)
Create database and linked server permissions on MS SQL Server where the BI4Dynamics DW will be deployed.
Granted Server role on MS SQL Analysis Services where the BI4Dynamics OLAP cubes will be deployed. Additionally, the Analysis Server service should run under BI4Dynamics domain account.
SQL Agent job Service user account needs all the above stated permissions.

Implementation options

Azure Analysis Services option:

Description	Value
Azure Analysis Services	asazure://westeurope.asazure.windows.net/bi4dynamicshybrid
Username (AAS admin)	mg@bi4dynamics.com
Password	Qpewoicsj490wkss

Azure SQL VM option:

Description	Value
RDP connection string (or RDP file)	vm-bi4-demo.westeurope.cloudapp.azure.com:3389
SQL server name (as seen in Azure)	vm-bi4-demo.westeurope.cloudapp.azure.com
VM Admin Account	vm-admin-user
VM Admin Account Password	6!dJ2yS34MbbQiPHs@rd

Use VM admin account for all permissions above.

1.2. Installing BI4Dynamics application #

⚠ Important notice:
IMPORTANT! Before installing BI4Dynamics, please check hardware and software requirements, make sure you have sufficient permissions and an active internet connected.
IMPORTANT! The installation process must be started on BI server where BI4Dynamics Data Warehouse will be created.

Install desktop BI4Dynamics application

1. Double-click BI4Dynamics.exe to start the installation*.

*Always start BI4Dynamics as an administrator.

Note: The name of the file varies based on the version you are using.

2. Follow the instructions on screen and Accept the license agreement.

Choose Default or Express option

The installer will check for some of the prerequisites. If you fail any of the tests you can click on the result to see the error. You must fix the problem before you can rerun the test. Once all the tests are successfully completed you can continue with the installation.

If a correct version of Shared Management Objects, Analysis Management Objects or .NET Framework are not installed, please click the Install button, which will trigger the installation of correct version of SMO’s, AMO’s, CLR Types or .NET Framework.

Select the folder where you would like to install BI4Dynamics. You can choose to create a shortcut on the desktop and/or in the start menu.

Confirm the configuration and begin the installation of BI4Dynamics.

Click Finish after the installation is completed.

1.3. Creating BI4Dynamics instance #

Deploying a BI4Dynamics solution is a six-step process:

Providing license information
Creating an instance
Adding the data sources
Selecting companies and global dimensions
Selecting modules
Deploying and processing model

When you open the application, an instance wizard will start to guide you through the configuration.

Step 1: Providing license information

Launch BI4Dynamics.exe from the folder where BI4Dynamics was installed to open the application.

Note: BI4Dynamics is 64-bit application, but the default installation location is C:\Program Files (x86)\BI4Dynamics NAV. Application can also be started from the Desktop shortcut or from the Start Menu shortcut.

Enter the BI4Dynamics online license key

Follow the instructions on screen to receive an offline license key (in case you do not have the internet connection or port 80 on the server is closed).

Note: If you experience any problems with authorization, please contact us via support@bi4dynamic.com.

Click Next.

Step 2 A: Creating a new instance with local Analysis Services

In this chapter we cover option where Analysis Services Database is deployed on local BI server.

Instance properties

Type the Name of the new instance.
Select what Language the solution will be deployed in.

SQL server

Keep or change Database Name of the BI4Dynamics Data Warehouse
Type SQL Server Name where the BI4Dynamics Data Warehouse will be deployed
(in case of Azure Virtual Machine: vm-bi4-demo.westeurope.cloudapp.azure.com)
Authentication type: Windows

Analysis Services

Keep or change the Analysis Database Name of the Analysis Services instance.
Keep or change the Analysis Database Server name where the Analysis Services model will be deployed.
Authentication type: Windows

If you plan to use Azure Analysis Services, follow Chapter 2 to install Azure Analysis Services and On-premises gateway. Come back when finished.

Step 2 B: Creating a new instance with Azure Analysis Services

This is the only step different in Azure Analysis Services installation compared to local Analysis Services.

This is the beginning of specificity step in Azure Analysis Services installation.

Continue within the same form and do the following setting:

Analysis Services

Analysis Server name: enter the name of the Azure Analysis Services

(asazure://westeurope.asazure.windows.net/bi4dynamicshybrid)

Authentication: Azure Active Directory
Username: email of the admin user that has been entered when creating Azure Analysis Services (adminuser@domain.com)
Password: enter password for Azure Active Directory

SQL server

This setting must be updated, here is why: As we are moving Analytics to Azure, we also need to move credential information to Azure Analysis Services. Authentication for SQL server will be saved to Azure Analysis Services string connection as Account impersonation. This is not needed if Analysis Services are on-premises.

When choosing Azure Active Directory in Analysis Services setup, a new Authentication window will open for SQL Server. Please enter

Username: same user in DOMAIN\adminuser format
Password: password for this user

This is the end of specificity step in Azure Analysis Services installation.

Step 2 continue: Adding and testing additional resources

This is the current setting on the form, and we continue with installation:

Local Analysis Services option

Azure Analysis Services option

Integration Services

Check SQL Integration Service to process BI4Dynamics using the Integration services.

Integration Services are used for parallel processing.

Refresh

Click Refresh to set default values for SQL Database file Locations (data and log files) and

SQL Database Collation are entered automatically. Modify if necessary.

This is how setting should look (showing local Analysis Services option):

Once all the fields are filled in, click Test Connectivity to check the credentials and connectivity to Database Server and Analysis Services. This should be the message:

Click Next.

Step 3: Adding Data Source

Azure Storage Information

This information is about Azure Storage Account that will be used to keep BC data. The values have been created during VM installation and are listed as a prerequisite for BC application installation.

Select On-Premise as the Data Source
Select the SQL Server Instance, where the source database is located
Select the Database Name of the source database
Choose Authentication type: Windows or SQL Server (with SQL Server Authentication you also need to provide your username and password)
Select your Dynamics source version – Data Source Version
Click Test to check if the entered information is correct

If the connection is successful as below, click Add to add the selected data source to the solution.

After that the icon with the data source should appear.

You can add more than one data source by clicking Add again.

Each data source you add will be colored differently.

Example of 2 data sources (BC 16 and NAV 2009):

Once the data source(s) Click Next.

You have successfully added Data Sources

Step 4: Selecting Companies and Global Dimensions

From the list of companies tick the ones you want to include in your instance and configure additional settings for each selected company:
- Company short name: User-friendly name (example UK 2016)
- Country: local country description – read from BC, no input
- Local Currency: local currency code (example GBP) – read from BC, no input needed
- Additional Currency: select the additional currency code for this company (example USD)
- Dimensions:
  1. Click Initialize global dimensions (delete the dimension by clicking the X button next to the dimension name if one or more dimensions will not be used in the project).
    We suggest adding a prefix or suffix to global dimension name, so that global dimensions are easier to find in the front-end tool (Excel, Power BI). Example below includes (dim).
  2. Click Add dimension to add the company dimensions and map it to corresponding global dimension. Mapping is done automatically, but you can manually change it if needed. (delete the dimension by clicking the X button next to the dimension name
  3. Continue adding and mapping dimensions for other selected companies. Only click Initialize global dimension if there are additional dimensions that are not present in the first company.
Click Next.

Step 5: Selecting Modules

Select Modules you would like to apply to your instance:

Click Next.

In the last step please click Close (do not click Run)

Move to Install tab and click Installation button.

Step 6: Installing and processing Data Warehouse and Analytics

Move to Install tab and click Installation button.

Click Run – while this is running, you can go and check execution on Log tab:

and wait for the “Installation Completed” message.

You have successfully installed and processed data warehouse and analytics.

2. Implementation option for Azure Analysis Services #

When you choose to install analytics as Azure Analysis Services, you need to install following:

Azure Analysis Service in Azure portal
On-Premises Gateway on your BI server

We recommend to first install On-premises Gateway and later Azure Analysis Services as we enter parameters from on-Premises gateway to Azure Analysis Service settings.

2.1. Installing On-premises Data Gateway #

On-premises data gateway is needed when connecting to Azure Analysis Service. This requires two steps:

Install On-premises data gateway on local computer
Configure On-premises data gateway field in Azure Analysis Services by selecting the On-Premises data gateway

Install On-premises data gateway on virtual machine

Download On-premises data gateway to virtual machine

Download On-premises Data gateway from Microsoft site:

https://www.microsoft.com/en-us/download/details.aspx?id=53127

Install On-premises data gateway

Follow the documentation from Microsoft site:

https://docs.microsoft.com/en-us/data-integration/gateway/service-gateway-install

ⓘ Note: Please be very careful when selecting the right region. Installation process will set On-Premises Gateway to your default region, that may not be the same as Azure Analysis Services. The feature is not so exposed during installation so it can easily go unnoticed.

If you have set the wrong Region, and your Gateway does not appear in the available list of gateways of your Azure Analysis Services than you must re-install On-premises data gateway.

Setup On-premises Data Gateway as Azure Service #

Go to Azure portal

Click on icon On-premises Data Gateway

Click on + Create

Enter all fields:

Resource name
Subscription
Resource group
Location
Installation name: select on-premises gateway that you have created in previous step from the list of available gateways

Then click Create

Click Go to Resource:

You have successfully created an On-premises Data Gateway as Azure Service.

On-premises Data Gateway must be selected on Azure Service like Azure Analysis Services that will use this gateway to receive On-premises data.

2.2. Creating Power BI Premium workspace #

Open Power BI portal, press Workspaces and Create a workspace.

Name the workspace and select Premium per user under License mode

Navigate to your Workspace

Press Settings button

Select Premium tab and Premium per user option and copy Workspace Connection.

Link should look like that: powerbi://api.powerbi.com/v1.0/myorg/BI4Dynamics%20PPU%20TEST

2.3. Completing Power BI Premium option installation #

Open SQL Server Management Studio and connect to Power BI workspace. Newly created analysis database should have tables, which are currently still empty since data is not yet processed.

Connect to local Database SQL server to check that instance database has been created.

The next step is to add this database as datasource.
To do that open Power BI portal Settings and navigate to Manage gateways.

Choose option Data Sources and press +New.

Then fill in the following:

Choose Gateway cluster name (created during installation of On-premises data gateway)
Specify the Database and Data Source names (in our case they are the same for simplicity)
Choose Data Source Type as SQL Server
Specifyyour local Server name
Enter Authentication method as Windows and insert your credentials
Click Create

Make sure that connection was established successfully.

Relate Power BI database with gateway datasource. To do that navigate to your workspace.

Press three-dot sign and select Settings from the pop-up menu

Under Gateway connection select the gateway you have installed on your computer and map gateway to the datasource used, click Apply.

You have successfully created Gateway Connection

2.4. Installing Azure Analysis Services #

Analysis Services can be deployed on:

A: Same computer that is hosting Data Warehouse (VM or Local Server)

Use this for BI development instance and when users connect to service that is running in LAN (local area network)

B: As Azure Analysis Services

Use this option (also described as Hybrid option) for most Production environments where users connect to service with AAD (Azure Active Directory).

In this chapter option B is described.

Create Azure Analysis Services

Before installing BI4Dynamics app you need to have available Azure Analysis Services that will host BI4Dynamics database.

Go to Azure portal, find Analysis Services and click +Add

Enter the following fields:

Server name: unique name of Analysis Server
Subscription
Resource group
Location
Pricing tier

Database size (GB) will depend on data type, cardinality, and number of rows in your data warehouse

QPUS (number of processing units) will depend on database size and number of users that will query the data

Suggestion: start with lower tier, check if data can fit into database and if response time from your BI tool (Excel or Power BI) is good enough. If not, go for higher tier. It takes 60 seconds to change tier to next level.

Click Create

You have successfully created Azure Analysis Services

Go to resource and copy Server name to notepad as it will be used in BI4Dynamics app installation.

In our example server name is: asazure://westeurope.asazure.windows.net/bi4dynamicshybrid

Connect to Azure Analysis Services

To verify installation, connect to Analysis Services Server use SSMS (SQL Server Management Studio) and enter:

Type Server Type: Analysis Services
Type Server name
TypeAuthentication: Azure Active Directory
Type Username
Type Password

Here is a database that we have just created:

Select On-Premises Data Gateway

On-Premises Data Gateway is needed when Azure Service received data from On-Premises. When Azure Analysis Services is receiving data from On-premises source than this option must be selected.

Click on On-premises data gateway

And Pick an On-Premises Data Gateway to connect

Click Connect selected gateway.

Gateway is now connected:

3. Managing instance #

3.1. Edit process flow #

Process flow is used to determine the sequence of execution of stored procedures when processing data with BI4Dynamics. Editing process flow is completely optional and it is usually connected with custom development.

Explanation of process flow top form functionality

Edit existing Process flow or Add a new one
Select Full or Incremental Processing type
Process All (Stage, Data Warehouse and Analysis Database)
Process just one on the above.
Create SQL Agent Job (with the specified periodicity and start date)
Process only Account Schedules instead of entire Analysis Database

If you click on Edit SSIS, you will have the following tab and options on it:

Reset the Process flow (return to original state) or Reload Process flow (include stored procedures saved in BI4Dynamics folder structure)
Create a new Object Group (standard 1-13)
Add or Remove an SSIS package from file

Explanation of process flow table functionality

Object group name.
All stored procedures within object group are executed before the next Object group is executed. Each Object group forms a separate SSIS package which is executed on processing.

Select/unselect a stored procedure to be executed on processing
Run the stored procedure manually from the application
Object subgroup name. Stored procedures within an Object subgroup are executed successively, while different Object subgroups within same Object group are executed in parallel.
Rename Object Subgroup option

Moving stored procedure from one object group to another

Right-click on a stored procedure gives you an option to move it to a different Object group if needed (on reset Process flow the stored procedure will be moved back to original Object subgroup).

Setting up process flow property manually in stored procedure

Permanently moving a stored procedure to a different Object group is possible by adding a DECLARE clause in the stored procedure itself:

By adding or changing declare statement store procedure’s execution flow is determined.

3.2. Manage Partitions #

Partitions divide portions of data you need to process (refresh) frequently from data that can be processed less frequently. For example, a fact table may include certain row sets that contain data that rarely changes, but other row sets have data that changes often. There’s no need to process all the data when only a portion of it needs to be processed.

Partitions work by dividing a table into logical partition objects. Individual partitions, each containing a unique segment of data, can then be incrementally processed either sequentially or in parallel independent of other partitions, or excluded from processing operations altogether.

This feature can be only used with SQL server enterprise edition or with Power BI Premium.

Open the Customize ribbon
Select Manage Partitions

Select the Cube.
Select the Fact.
Select the Date field to Partition by.
Select the number of last partitions processed (this is determined by how much backdating is done).
Deploy the fact.
Process.

3.3. Setup BI4Dynamics precision #

Numeric data types can be stored in SQL with different precision, scale, and length.

Precision is the total number of digits that can be stored both to the left and right of the decimal place.

Scale is the number of digits to the right of the decimal point in a number.

Length is the number of bytes that are used to store the number.

In SQL the default maximum precision and scale for a particular length is the following:

19,5 = 9 bytes
27,10 = 13 bytes
38,20 = 17 bytes

During the installation, the following error might occur on stage:

It should be addressed by increasing the precision (e.g. from 19 to 38) and scale (e.g. from 5 to 20).

The default maximum precision of numeric and decimal data types is 38.

To change it in application you need to:

Open the instance and click File > Properties.

Under Instance Setup > Data Warehouse Properties precision could be set up for:
Stage tables
Datawarehouse tables

3.4. Adding Calculation groups #

Open Tabular editor
Go to File > Open > From DB
Select the Server and Instance where you want to create calculation groups
Select Model and New Calculation group

Add new Calculation items with DAX query

Right click the new Calculation group, select Script > Create or Replace > To Clipboard

Open BI4Dynamics application and open instance
Go to Customize tab > Manage Dimensions

Select New Calc. Group, add a name and copy the script

Update and Generate all under Calculation groups
Deploy and Process analysis database

3.5. Deleting instance #

Open the instance you would like to delete by clicking File and selecting the instance in the list on the right.
Click File > Delete.
Click Yes to confirm the action.

IMPORTANT! When deleting an instance, the Data Warehouse and Analysis Database will be deleted.

Note: BI4Dynamics Instance folders and the log file are not removed during the delete process.

Uninstalling BI4Dynamics

Click Start > Settings > Control Panel.
Click Add or Remove Programs.
From the list of installed programs, select BI4Dynamics.
Click Remove.
Follow the instructions on screen.

IMPORTANT! User files will not be removed when uninstalling the solution.

4. Connecting Excel and Power BI reports #

4.1. Downloading Excel and PowerBI reports #

Open Instance
Go to Install tab
Click Download reports
Select the destination
Standard reports will be downloaded that are included with BI4Dynamics

4.2. Connecting Excel reports #

Changing connection

Open Excel report
Click Data > Connections > Properties >Definition
Type the database name to the Initial Catalog property of the Connection string. Type the Server name to the Data Source property of the Connection string.