...

BI4Dynamics Application – Installation Manual for BC & NAV on Local or Azure Virtual Machine

Updated on November 26, 2024

1. Installation #

Installation documentation consists of documents:

  1. Installation of BI server on Local Machine or Azure Virtual Machine (VM)
  2. Installation of BI4Dynamics application – this process is covered by this document
  3. 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:

  1. Be (local) administrator on the windows server where BI4Dynamics is installed.
  2. Read permissions on the BC database.
  3. Read permissions on the master database of the source database SQL Server (only required if the BC license is uploaded to the SQL server)
  4. Create database and linked server permissions on MS SQL Server where the BI4Dynamics DW will be deployed.
  5. 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.
  6. 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:

  1. Providing license information
  2. Creating an instance
  3. Adding the data sources
  4. Selecting companies and global dimensions
  5. Selecting modules
  6. 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

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

OR

  • 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

  1. Type the Name of the new instance.
  2. 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

  1. Keep or change the Analysis Database Name of the Analysis Services instance.
  2. Keep or change the Analysis Database Server name where the Analysis Services model will be deployed.
  3. 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.

  1. Select On-Premise as the Data Source
  2. Select the SQL Server Instance, where the source database is located
  3. Select the Database Name of the source database
  4. Choose Authentication type: Windows or SQL Server (with SQL Server Authentication you also need to provide your username and password)
  5. Select your Dynamics source version – Data Source Version
  6. 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

  1. 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.
  2. 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:

  1. Install On-premises data gateway on local computer
  2. 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 #

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

  1. Edit existing Process flow or Add a new one
  2. Select Full or Incremental Processing type
  3. Process All (Stage, Data Warehouse and Analysis Database)
  4. Process just one on the above.
  5. Create SQL Agent Job (with the specified periodicity and start date)
  6. 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:

  1. Reset the Process flow (return to original state) or Reload Process flow (include stored procedures saved in BI4Dynamics folder structure)
  2. Create a new Object Group (standard 1-13)
  3. 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.

  1. Open the Customize ribbon
  2. Select Manage Partitions
  1. Select the Cube.
  2. Select the Fact.
  3. Select the Date field to Partition by.
  4. Select the number of last partitions processed (this is determined by how much backdating is done).
  5. Deploy the fact.
  6. 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:

  1. 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 #

  1. Open Tabular editor
  2. Go to File > Open > From DB
  3. Select the Server and Instance where you want to create calculation groups
  4. 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
  1. Update and Generate all under Calculation groups
  2. Deploy and Process analysis database

3.5. Deleting instance #

  1. Open the instance you would like to delete by clicking File and selecting the instance in the list on the right.
  2. Click File > Delete.
  3. 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

  1. Click Start > Settings > Control Panel.
  2. Click Add or Remove Programs.
  3. From the list of installed programs, select BI4Dynamics.
  4. Click Remove.
  5. 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 #

  1. Open Instance
  2. Go to Install tab
  3. Click Download reports
  4. Select the destination
  5. Standard reports will be downloaded that are included with BI4Dynamics

4.2. Connecting Excel reports #

 Changing connection

  1. Open Excel report
  2. Click Data > Connections > Properties >Definition
  3. 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.

 Creating a new connection

  • Open Microsoft Excel
  • Click Data > Get External Data > From other Data Source > From Analysis Services.
  • Insert a Server name                                      

do not enter “.” for local server as this connection will not work on another PC

  • Click Next and select Analysis database from the dropdown menu and select Model
  • Click Finish and OK on next form.
  • Start exploring your data by dragging and dropping dimensions and measures in pivot table

You have successfully connected Excel to Analysis Services model.

4.3. Connecting Power BI reports #

 Changing connection

  1. Open Power BI report
  2. Click Edit Queries > Data source settings
  • Type the Server name to the Server field. Type the Database name to the Database field

 Creating a new connection

  • Open Power BI Desktop
  • Click Get data
  • Choose Analysis Services
  • Type the Server name and Database name
  • Choose Connect live
  • Click OK and start exploring your data

You should see this screen:

You have successfully connected Power BI to Analysis Services model.