Sales Cube Setup Guide

This section provides simple step by step instructions on how to setup the infrastructure without going into the details. This might be useful for the initial setup during the evaluation for the users not yet familiar with all concepts.

Step 1: Installation

Magento 1 extension files can be downloaded from the www.bimproject.net after purchasing the license. Read “Installation” section of this documentation to get more information on how to install the extension on Magento 1.

Magento 2 extensions are distributed using private composer repository. Credentials required to access the repository are available on the website details page on www.bimproject.net.

To install the extension on Magento 2 store, perform the following steps (after purchasing of the extension license): 

1. login to your account on www.bimproject.net
2. Go to “My Websites” and configure the website.
3. Go to “My Licenses” and assign the license to the website.
4. On the website settings page you may find access data for the composer repository.
5. Configure your composer and add repository URI to composer.json file:

[{
    "type": "composer",
    "url": "http://www.bimproject.net/en/bim_repo/composer/index"
}]

6. Add authentication to auth.json:

{
    "http-basic": {
        "www.bimproject.net": {
            "username": "<user from website settings page>",
            "password": "<password from website settings page>"
        }
    }
}

7. Install extension to Magento 2:

composer require bim/pbi-sales-cube-metapackage-magento2:2.2.4.1
bin/magento setup:upgrade
bin/magento setup:di:compile

Read “Installation” section of this documentation to get more detailed information on how to install the extension on Magento Platform.

Step 2: Setup DB Connections and crontab

1. Create new empty database in MySQL 5.6 or MySQL 5.7.
2. Add connection information for the new empty database to app/etc/env.php file of Magento 2 installation under connection name "bim_dwh". On Magento 1 the connection information should be added to app/etc/local.xml file.
3. Add connection information for reading data of Magento. Please, copy "default" configuration and add it as "bim_data_export".

# File app/etc/env.php in Magento 2
...
    'db' => [
        'connection' => [
            'default' => [
				...
            ],
            'bim_dwh' => [
                ...
            ],
            'bim_data_export' => [
                ...
            ]
        ]
    ],
    'resource' => [
        'default_setup' => [
            'connection' => 'default'
        ],
        'bim_dwh' => [
            'connection' => 'bim_dwh'
        ],
        'bim_data_export' => [
            'connection' => 'bim_data_export'
        ]
    ],
...

# File app/etc/local.xml in Magento 1
...
	<resources>
		<default_setup>
			<connection>
				<host><![CDATA[localhost]]></host>
				...
			</connection>
		</default_setup>
		<bim_dwh>
			<connection>
				<host><![CDATA[localhost]]></host>
				...
			</connection>
		</bim_dwh>
		<bim_data_export>
			<connection>
				<host><![CDATA[localhost]]></host>
				...
			</connection>
		</bim_data_export>
	</resources>
...

4. Run by crontab the following command every minute:

# For Magento 2
bin/magento bim:etl:scheduler

# For Magento 1
shell/bim_etl.php scheduler

Step 3: Configuration

1. Obtain extension License Key from Website Details page on bimproject.net and enter this key in the configuration of Magento Platform.
2. Register App for Power BI in Azure Active Directory and obtain Client ID and Client Secret. Enter the Client ID and Client Secret to configuration of Magento Platform.
3. Configure the Live scheduler to run every 2 minutes (recommended interval for initial setup). This could be configured in Magento configuration pages on BIM Extensions -> Data Integration (ETL) -> Scheduler tab.

Step 4: Connect to Power BI Service

1. Open Datasets Page (Reports -> Datasets). Click on “Connect to Power BI API” button. Provide user and password for Power BI to connect the user with Magento Platform.
2. Enable automated data actualization for the user on the same page (Reports -> Datasets).

Step 5: Create Permanent Dataset

1. Open Datasets page.
2. Click on “New”.
3. Select “Sales Cube” if asked to select the dataset type.
4. Fill in the dataset properties and save the dataset.

Do not Load the Dataset yet if the store has lot of data! Initial load might take significant time (up to 1 day).

Step 6: Publish dataset

Publish the created Sales Cube dataset by clicking on “Publish” button on dataset properties page.

Please, not that this will crease dataset in Power BI, but will not populate it with data yet.

STEP 7 (optional): estimate amount of data

For the stores with log of data, do the estimation of the amount of data in the dataset to make sure, it does not go over the limitations of Power BI:

1. Open Reports -> BIM Development Tools
2. Open Sales Cube Capacity estimation tool.
3. Check if there are no tables which exceed the capacity. Also, the page will show estimated time it would take to load all data to Power BI.
4. If there are tables that exceed the allowed capacity, adjust dataset properties to reduce the timeframe or limit amount of stores in the dataset.
5. Once you will make sure that capacity is not increased, proceed further.

Step 8: Load data to the dataset

Trigger the start of data loading. Depending on the amount of data, it may take different time depending on the amount of data and the Power BI account limitations.

To start loading of the data:

1. Open Dataset properties in Magento Platform.
2. Click to “Start Data Loading” button.
3. Refreshing the page will update information in the data loading status field.

To load the dataset manually click on “Load” button on dataset properties page inside Magento Platform (only recommended for the cases when dataset does not have lot of data).

Step 9: Configuring scheduled data updates

Once the initial load is done, user can configure the desired interval for data actualization by configuring Live or Regular schedules and assigning the dataset to this schedule. Additionally it is recommended to configure the clean-up schedule. All created datasets will update full set of data by the clean-up schedule.

1. Configure the desired schedules configuration on the Magento Configuration page, section BIM Extensions -> Data Integration (ETL) -> Scheduler.
2. Change dataset properties to assign dataset to regular or live scheduler if it is not yet done.
3. Make sure, auto load is enabled for the user (is done in previous steps).

Step 10: Publish Report Templates

Step 10 and 11 are only for Full version of the Sales Cube package that includes the templates and the embedding functionality.

After the data will be delivered to PBI (or will start to be delivered), user can upload reports and configure embedding functionality.

1. Navigate to Reports -> PBI Templates menu inside Magento Platform.
2. Upload each report related to Sales Cube and assign it to newly loaded dataset.

Step 11: Configure embedded content

1. Open Magento Platform configuration page and navigate to BIM Extensions -> Power BI Integration -> Embedding
2. Under the Embedding section, enable all embedded content and specify the datasets and reports for embedding. You will need to know Dataset ID, Report ID, Workspace ID from Power BI Service. Easiest way is to find this information in the URL after navigating to particular Power BI content in Power BI Service.