SALES CUBE USAGE

WHAT IS SALES CUBE DATASET?

Power BI Integration Extension may be supplied with the support of Sales Cube dataset type (Sales Cube Dataset). Datasets of this type are self-containing and does not require user to create Table Profiles. The Sales Cube Dataset delivers wide range of data related to sales process. All tables and their data is linked with the dataset type and all what user needs to do to deliver extensive set of data related to sales is to create dataset, publish it and let the data to be loaded.

More detailed information about the data included in the Sales Cube could be found in Sales Cube Data Dictionary document.

CONFIGURING SALES CUBE ETL SETTINGS

There are additional options related to data extraction logic in Magento Configuration (Stores Configuration in Magneto 2 or System Configuration in Magneto 1), section Insights Ready Data Integration (ETL) Data Extraction.

Prefer Native Query export option is controlling the way the data will be extracted from Magento Platform. When this option is enabled, the logic that extracts data from Magento will try to use SQL queries instead of relying on Magento standard data reading process which might not be well optimized for intensive data extraction process. This option is enabled by default. Disabling this option is not recommended. Only disable this option in case if Native Query cannot be used for some reason.

Use unbuffered query mode for Native Query export option will change the connection properties used to extract data from Magento Database. Unbuffered query mode is more preferable for data extraction logic. When this option is enabled, memory consumption may increase on the database server. Disabling this option will increase memory consumption of Magento Platform (PHP). This option is enabled by default. Disabling this option is not recommended. Only disable this option in case if unbuffered query cannot be used for some reason.

Neither of options should have impact on core functionality of Magento Platform.

CONFIGURING SALES CUBE DATASET

Additionally to standard Dataset properties, Datasets of Sales Cube type will have some additional settings on Dataset Properties page. Those settings allow decreasing amount of data in the dataset. This might be desired in case if complete data will go over limitations of Power BI Service.

Minimum History Range property allows limiting the period of history that will be included into the dataset.

Stores option allows selecting list of Magento stores that the data of the dataset will cover.

SALES CUBE DATA LOADING

As mentioned before it is easy to use Sales Cube Dataset. But in case if dataset has lot of data, loading process may take quite some time. The dataset includes multiple tables, some of which may have several millions of rows. With Power BI Pro subscription users can upload data on the rate of 1 million rows per hour as maximum. And this limit cannot be increased (maybe only with Premium capacity based subscription).

Initial load of single Sales Cube Dataset may take more than 24 hours. For this reason it is not recommended to trigger initial loading of Sales Cube dataset using Load button in the Magento Platform, but let it be loaded by schedule.

Once the initial load will finish, all consequent loads will load only newly added data and this will be done faster.

The following actions are recommended to perform initial loading of the data:

1. Create Dataset with Sales Cube type.

2. Publish Dataset.

3. Configure dataset with Load Type set to Live schedule.

4. Enable Live schedule (in Magento Configuration).

5. Enable Auto Data Actualization for current user (on Datasets List page).

6. After waiting for the next scheduled data load, data should start loading process. This can be seen in ETL Jobs page (Reports ETL Jobs).

Additionally user might be able to estimate the amount of data in the dataset and time it will take to load the full dataset using Dev Tools. More detailed it is described in "Advanced Features: Usage of Dev Tools: Sales Cube Dev Tools" section of this documentation.

REPORT TEMPLATES FOR SALES CUBE DATASET

Sales Cube functionality comes with set of reports that could be uploaded to Power BI Service and assigned to the Sales Cube Dataset.

At the moment the following report templates are delivered with Sales Cube:

1. SC Embedded Customer Statistics - Report for embedding as customer statistics page.

2. SC Embedded Order Statistics - Report for embedding as order statistics page.

3. SC Embedded Product Statistics - Report for embedding as product statistics page.

4. SC Mage Order Taxes Report - Replication of Magento Order Taxes Report.

5. SC Mage Total Invoiced vs Paid Report - Replication of Magento Total Invoiced vs Paid Report.

6. SC Mage Total Ordered Report - Replication of Magento Total Ordered Report.

Read "Embedding Power BI Content" for the information on how to upload the reports to Power BI Service.

Additional reports can easily be created inside Power BI Service.

SALES CUBE DATA CLEAN-UP SCHEDULE

Every created and published Sales Cube dataset will be automatically processed by Clean Up scheduler. The schedule for the Clean Up could be configured on the Magento Configuration page, section Insights Ready Data Integration (ETL) Scheduler.

The clean-up fill trigger full refresh of the data inside the Sales Cube dataset. This is required to actualize data with recent changes of product/category names and other attributes that cannot be easily updated inside Power BI after being published.

Set Enable clean-up option to "Yes" to enabling execution of the clean-up jobs.

Configure Clean-up Frequency with desired frequency for the clean-up jobs: weekly, monthly or once in two months.