System Requirements Specification

November 2018 Version 2.1.3

Content

• Definitions
• Packages Overview
• Business Drivers
• Business Model
• Business and System Use Cases
• System Requirements
  • PHP Version
  • Database
  • OS
  • Memory
• Functional Requirements
  • Power BI Integration Extension
  • Power BI Integration Plus Extension
  • Power BI Sales Cube Extension
• Technical Requirements (non-functional)
  • Influence on standard functionality of Magento Platform
  • Scheduled Data Processing
  • Sales Cube Data processing time
    • Data Extraction Time
    • Data Transformations Time
    • Data Load to Power BI Service
  • Response time
• Development and debugging capabilities
• Constraints (limitations) and Assumptions
  • Limitations of Power BI Integration (Plus) Extension
  • Limitations of Power BI Sales Cube (Lite) Extension

Copyright Notice

This documentation and the described software are copyrighted, 2018, by Kyrylo Kostiukov. All rights reserved. Kyrylo Kostiukov reserves the right to make improvements to the products described in this manual at any time without notice.

No part of this User Guide may be reproduced, copied, translated or transmitted, in any form or by any means without prior written permission of Kyrylo Kostiukov. Information provided in this User Guide intended to be accurate and reliable However, Kyrylo Kostiukov assumes no responsibility neither for its use, nor for any infringements of rights of third parties which may result from its use.

PREFACE

This document describes the features and behavior of the Software. It includes a variety of elements that attempts to define the intended functionality required to satisfy different users of the Software. Functionality described here might be covered by Support Services Term and Conditions agreement between consumer of the software and the software provider.

In addition to specifying how the system should function, the specification also defines the main business processes that will be supported, what simplifying assumptions have been made and what key performance parameters will need to be met by the system.

The document covers functionality of different but related Software Packages where one package might extend functionality provided by the other packages. If not stated explicitly, the written below relates to all Software Packages described in this document.

Definitions

“Power BI Service Dataset” or “Remote Dataset” means dataset created in Power BI Service via REST API, sometimes referred as “Online Dataset” in Power BI community. Dataset includes in itself Power BI Tables and holds the configuration of relations between such tables.

“Power BI Table” means a table inside Power BI Service Dataset. Table consist of Columns and holds the data delivered to Power BI.

“Dataset” means dataset configuration of a Power BI Service Dataset inside Magento Platform. This configuration does not need to be associated with Remote Dataset, but could be just a template for the future “Remote Dataset”. Dataset might be published to Power BI Service and in which case new Remote Dataset will be created and linked with the Dataset being published.

“Power BI Table Profile” or “Table Profile” is a template of the future Power BI Table. It is linked with Dataset and is published together with the Dataset it is associated with.

“To load Dataset” or “to load Table Profile” means loading of the data to Power BI Service and respectively loading data to associated Remote Dataset and associated Power BI Tables.

“Magento Platform” means Magento ecommerce platform.

“Magento Reports” means reports that come out of the box with Magento Platform.

Packages Overview

This document describes requirements specification of the following products (packages):

• Power BI Integration Extension
• Power BI Integration Plus Extension
• Power BI Sales Cube Lite Extension
• Power BI Sales Cube Extension

Power BI Integration is an extension for Magento e-commerce platform (“Magento Platform”) that integrates Magento Platform with Power BI Service from Microsoft (“Power BI Service”) via Power BI REST API. It allows delivering data from reporting pages of Magento Platform to Power BI Service and automatically updates this data according to predefined schedule.

Power BI Integration Plus is an extension for Magento Platform that extends functionality of Power BI Integration. It allows configuring embedded content that will come from Power BI Service and appear as an integral part of Magento Platform on different administration pages of Magento Platform. Additionally, it has a higher degree of flexibility in terms of manipulation the data in Power BI Reports such as download/upload/assign/re-assign of Reports.

Power BI Sales Cube (“Sales Cube”) is an extension that includes functionality of Power BI Integration and Power BI Embedded and on top of it, delivers a high-quality data model into Power BI Service. Instead of relying on reporting data available in Magento Platform, Sales Cube comes with alternative implementation of reporting data processing logic intended to ensure high quality, low latency and efficiency of analytical data and to provide more flexibility in data analysis. This allows having in the end comprehensive data model inside Power BI Service covering different aspects of the sales data that comes from Magento.

Full list of functions of each Extension will be described later in this document.

High level overview over the features of different packages could be found in the table below:

Feature	Power BI Integration	Power BI Integration Plus	Power BI Sales Cube Lite	Power BI Sales Cube
Deliver Magento Reports data from Magento Platform	Yes	Yes	No	Yes
Scheduled update of uploaded Dataset	Yes	Yes	Yes	Yes
Publish dataset to specific Power BI Group (Workplace)	Yes	Yes	Yes	Yes
Embed Reports to Magento Platform pages	No	Yes	No	Yes
Download (Backup) Power BI Reports	No	Yes	No	Yes
Upload Power BI Reports and assign them to any Dataset	No	Yes	No	Yes
Deliver high quality Sales Cube dataset optimized for Power BI	No	No	Yes	Yes
Iterative data export from Magento Platform	No	No	Yes	Yes
No need to purchase Power BI Pro or Premium subscriptions (with limitations)	Yes	Yes	Yes	Yes

Business Drivers

Reporting has always been an important activity of any business. It facilitates communication and decision making on different managerial levels.

As the business grows, the number of stakeholders, complexity of the management hierarchy and amount of data increase respectively. Therefore, the need to have well defined KPIs arises. Reporting on business specific KPIs assures transparency and allows efficient monitoring of crucial business activities.

Manual preparation of Reports is time consuming activity, especially if the same report needs to be prepared again and again every year, month, week. Manual data processing may lead to different interpretation of the numbers across different time periods, may introduce mistakes in calculation. Modern business is looking for the ways to automate this process and make it more efficient, have more reliable data and increase frequency of reports updates to days and sometimes to minutes.

Proper business intelligence solution helps to satisfy the need of modern business for efficient reporting. Interactive reports help to discover new patterns, do ad-hoc analysis, keep an eye on the most critical business KPIs independently on where you are, communicate with colleagues and stakeholders in a completely new way, supporting the process of communication with new impressive technologies, allowing any company to be empowered by the world of modern technologies helping the business to succeed.

Getting the right numbers in the right time is essential for success of any company. Having properly organized reporting will help to solve common problems most companies are dealing with by:

• Reducing the time people spend on reports creation
• Improving quality of the reports by making sure the reports are based on the right data
• Reducing the costs and involvement of IT personal into reports creation and data preparation
• Being able to easily modify and extend the existing reports by adding more data
• Being able to create interactive reports that do not only allow to see one slice of data, but also to interact with it and get more details
• Having common approach in reports creation across the company, being able to share reports with others.
• Automating process of actualization of the reports with actual data
• Allowing to access and interact with reports using mobile devices

The integration with Power BI will provide possibility to enjoy using one of the most modern BI solutions available today.

Accurate and professional reporting will help companies to:

• Accelerate and improve decision making,
• Optimize internal business processes,
• Increase operational efficiency,
• Drive new revenues,
• Gain competitive advantages over business rivals,
• Identify market trends,
• Spot business problems that need to be addressed.

Business Model

The software is intended to help companies that use Magento to setup BI infrastructure around the data inside their Magento Platform.

Classical Data Warehousing approaches include approaches based on central Enterprise wide Data Warehousing and approaches where each area of business may have their own independent Data Mark solution. Both approaches have their benefits, both have their costs.

With Power BI Integration solutions organizations may setup their Data Mark solution around sales data from Magento Platform without the need to invest lot of time and money into development.

There are following types of companies, who can benefit from the Power BI Integration solution, all having Magento Platform in place:

1. Companies that do not have yet any BI solution in place. With integration to Power BI, company gets an opportunity to use one of the best BI solutions available now on the market for the fair price and without the need to make huge investments in development.

2. Companies having their non-Microsoft BI solutionnot integrated yet. Power BI integration with Magento Platform may help to have a cost efficient solution based on Data Mart model. In case if a company is willing to integrate its own BI solution and have central Data Warehouse, it may take years to have it integrated.

3. Companies with non-Microsoft BI solution already integrated with Magento Platform. Power BI Integration extensions may be more efficient than a current solution and provide more possibilities in customizing the solution for customer needs. Another reason to go for Power BI solution might be that a current solution does not provide enough functionality comparing to Power BI. Power BI is growing very fast and has already established itself as market leader placing a lot of BI vendors behind.

4. Companies already using Power BI Service. Such companies have both vendors already in place and may benefit from integrating those two Platforms together.

Business and System Use Cases

There are three user types who may interact with the Software and benefit from Power BI Service integrated with Magento:

• Magento Administrator – Magento administrator may restrict access to certain functionality and allow only dedicated users to use Power BI Integration. In case if Power BI Embedded is used, it might also be possible to restrict access to creation of Power BI Content and allow read-only access to the Power BI content which is embedded into Magento Platform.

• Report Creators –users who are willing to deliver data from Magento Platform and create interactive reports and dashboards based on that data.

• Report Consumers with Power BI Pro account – users who may get access to created Reports that were shared with them either from Power BI Service or, if Power BI Embedded extension is used, from inside the Magento Platform.

• Report Consumers without Power BI Pro Account – such users cannot get access to shared reports inside Power BI Service, but it is possible to share content with such users within Magneto Platform using Power BI Embedded extension.

The main use case for Power BI Integration is to create Power BI Service Dataset, connected with Magneto Platform and updated on predefined schedule.

Once users have their Power BI Service Dataset (“Dataset”) with Magento Platform’s data, it can use it as follows:

• Create interactive Reports and Dashboards from Power BI Service
• Share created Reports with other Power BI users *
• Connect to the Dataset using Power BI Desktop and create calculated measures using DAX formulas
• Publish Reports created with the Dataset using Power BI Desktop back to Power BI Service
• Access the Reports and Dashboards from Power BI Mobile App
• Setup Email subscriptions, Alerts
• Export data in the excel format from Power BI Service
• Access dataset from Question and Answer (“QnA”) functionality of Power BI
• Allow Cortana to access the dataset.
• … and use many other functionality available inside Power BI

* sharing functionality inside Power BI Service only available between Power BI Pro users.

Users who have Power BI Embedded extension will get more control over their content and will get possibility to embed reports inside Magneto Platform:

• Share Power BI Content with Magento Platform users who does not have Power BI account
• Download Reports(backup) from Power BI Service to Magneto Platform
• Upload Reports (backups, templates) from Magento Platform to Power BI Service
• Assign Reports to another Datasets
• Embed Reports on administration Order, Customer, Product details pages.
• Embed QnA section inside Overview (Home) page of Magento Platform.

Power BI Sales Cube extension will supply comprehensive high quality data from Magento Platform allowing users to fully utilize flexibility and intelligence of Power BI solution.

System Requirements

PHP Version

Supported PHP version depends on the list of supported Magento Platform versions. If the PHP version is listed as supported by those Magento Platform versions, it should also be supported by the extension. Exception: PHP versions below 5.5 are not supported.

Database

Power BI Sales Cube Extension only supports Magento Platforms running on MySQL database MySQL database version should correspond to the one specified in System Requirements for the Magento Platform.

DWH for Power BI Sales Cube Extension can be run on a separate database server. Supported versions for the DWH are MySQL 5.6, MySQL 5.7.

MySQL server should be configured with innodb_log_buffer_size set to at least 16 MB.

OS

Only Linux based OS.

Memory

MySQL Database server for DWH of Power BI Sales Cube Extension should have at least 1 GB of memory for normal function.

Functional Requirements

Power BI Integration Extension

Once installed, Power BI Integration extension will provide the possibility to connect Magento admin user (“User”) to Power BI Service Account and deliver Magento Reporting data to Power BI Service.

In order to facilitate this, User should be able to do the following:

1. Setup connection with Power BI Service.

1.1. Configure Power BI REST API access by providing client id and client secret in Magento Configuration.

1.2. Connect its Magento User account to Power BI Service account. This will authorize Magento System to interact with Power BI Service on behalf of the User. The connection between Magento System will stay permanently, allowing the user to setup data delivery to Power BI while the user is offline.

1.3. Each Magento User may connect to its own Power BI Service account and create its own content (though, it is recommended to restrict amount of content created by different Users and share datasets between users using Power BI Workspaces as this will reduce amount of duplicated data that will need to stay synchronized with Magento).

2. Create Power BI Table Profiles.

2.1. User should be able to create Power BI Tables Profiles. Out of the box Power BI Integration extension comes with Power BI Table Profiles based on Magento Reports. Such Table Profiles are called Table Profile with type “report”.

2.2. Table Profiles are created from Magento Reports pages using “Export” button. Once created, Table Profile linked with Magento Report will keep set of filters specified by the user during creation.

2.3. Table profile might be modified after it has been created, but before it has been published. User can specify the name for the Power BI Table, give the name to Power BI Table Profile and assign the Table Profile to a Dataset that support the type of Table Profile being modified.

2.4. Table Profiles based on Magento Reports with date filter can be configured to specify or modify the date period allowing user to modify data range on the reports assign dynamic values for date range. Dynamic values will be calculated before every delivery of the data to Power BI. Dynamic date period gives user the possibility to specify date relative to the current moment (“now”, “-1 day”, “-2 weeks” and so on).

2.5. Below is the list of Magento Reports supported by the extension in Magento 1:

1. Total Ordered Report, Reports -> Sales -> Orders
2. Order Taxes Report Grouped by Tax Rate, Reports -> Sales -> Tax
3. Total Invoiced vs. Paid Report, Reports -> Sales -> Invoiced
4. Total Shipped Report, Reports -> Sales -> Shipping
5. Total Refunded Report, Reports -> Sales -> Refund
6. Coupons Usage Report, Reports -> Sales -> Coupons
7. Products in carts, Reports -> Shopping Cart -> Products in carts
8. Abandoned carts, Reports -> Shopping Cart -> Abandoned carts
9. Products Bestsellers Report, Reports -> Products -> Bestsellers
10. Products Ordered, Reports -> Products -> Products Ordered
11. Most Viewed (Products) , Reports -> Products -> Most Viewed
12. Low stock (Products) , Reports -> Products -> Low stock
13. Downloads, Reports -> Products -> Downloads
14. New Accounts, Reports -> Customers -> New Accounts
15. Customers by Orders Total, Reports -> Customers -> Customers by orders total
16. Customers by number of orders, Reports -> Customers -> Customers by number of orders
17. Customers Reviews, Reports -> Reviews -> Customers Reviews
18. Products Reviews, Reports -> Reviews -> Products Reviews
19. Search Terms, Reports -> Search Terms

Additionally, creation of Power BI Table Profile in Magento 1 is possible based on non-reporting lists (only one list is supported now):

1. Orders List, Sales -> Orders

2.6. Below is the list of Magento Reports supported by the extension in Magento 2:

1. Products in Cart
2. Products Views
3. Downloads
4. Shipping
5. Refunds
6. Orders
7. Invoiced
8. Coupons
9. Tax
10. Bestsellers
11. Abandoned Carts
12. Customer Totals
13. New Accounts

3. Create and publish Datasets.

3.1. User should be able to add/delete Datasets inside Magento Platform.

3.2. Dataset that already has Table Profiles assigned to it might be published from Dataset Management pages of Magento admin panel.

3.3. Once Dataset is published, user can load data to the Remote Dataset.

3.4. If the Dataset was not published yet, User may edit properties of the Dataset and change the Dataset’s name.

4. Setup scheduled data synchronization.

4.1. User should be able to configure different schedule types: live and regular. Live scheduled might be configured with frequency of several times per day. Regular schedule might be configured with frequency more of once per day or more often.

4.2. Each dataset might be assigned to Live or Regular schedule.

4.3. User can enable/disable Auto Loading of the data for his Magento User Account on Datasets Management page.

4.4. Data of the Dataset should be automatically updated if: Auto Load is enabled for Magento Account owning the Dataset, Magento Account is connected to Power BI Account, Dataset is published and Dataset is assigned to a schedule which is active and configured.

5. 5. Configure scheduled Magento Statistics Refresh.

5.1. User should be able to configure automated refresh of Magento Statistics. This could be done by live or regular schedule and configured for Lifetime or Last-Day statistics refresh.

Power BI Integration Plus Extension

Power BI Embedded extension includes all functionality available in Power BI Integration. Additionally, the following functionality will be added:

1. Download/Upload of Power BI Reports as files with *.pbix extension.

1.1. User is able to download the Report based on Online Dataset.

1.2. User is able to upload the downloaded Report back to Power BI Service and assign any Online Dataset to this Report, but Dataset should be in the same workspace as the report. In case if the new Dataset will not have the same Tables and Measures, Report might be corrupted.

2. Embed Power BI Content into Magento Platform.

2.1. User should be able to activate QnA section on the Overview page of the admin panel and configure dataset from Power BI Service that will be used by the QnA widget. As a result, QnA section will be available on the Overview page of the admin panel where user will be able to type in the question and get answers provided as Power BI content.

2.2. User should be able to activate Statistics Tab on the product page in the admin panel and configure the report from Power BI Service that will be embedded in that tab. As result, the report will be presented on the product page under Statistics Tab. The Report will be filtered by “Product Id” field from “Products” table.

2.3. User should be able to activate Statistics Tab on the customer page in the admin panel and configure the report from Power BI Service that will be embedded in that tab. As a result, the report will be presented on the customer page under Statistics Tab. The Report will be filtered by “Customer Id” field from “Customers” table.

2.4. User should be able to activate Statistics Tab on the order page in the admin panel and configure the report from Power BI Service that will be embedded in that tab. As a result, the report will be presented on the order page under Statistics Tab. The Report will be filtered by “Order Id” field from “Orders” table.

3. Access available Reports, Dashboards and Tiles from Magento Platform.

3.1. User should be able to navigate to Reports page where list of available reports will be displayed. User can open each report and it will be shown without redirection to Power BI Service.

3.2. User should be able to navigate to Dashboards page where list of available dashboards will be displayed. User can open each dashboard and it will be shown without redirection to Power BI Service.

3.3. User should be able to navigate to Tiles page where list of available tiles will be displayed. User can open each tile and it will be shown without redirection to Power BI Service.

Power BI Sales Cube Extension

Power BI Sales Cube Extension is very easy to use and requires minimum amount of efforts from the user in order to setup data delivery to Power BI Service. The functional requirements include:

• user interface of the Magento Platform that allow user to create and publish Sales Cube dataset;
• detailed description of the data delivered to Power BI Service and available Measures,
• report templates that might be uploaded to Power BI Service and embedded using Power BI Embedded extension.

Power BI Sales Cube Extension comes together with all functionality available inside Power BI Integration and Power BI Embedded extensions.

Power BI Sales Cube Lite Extension includes most of Power BI Integration functionality, but excludes Report based Table Profiles, support for content Embedding.

Power BI Sales Cube extension includes the following additional functional requirements:

1. Creation and publishing of Sales Cube Dataset.

1.1. Creation of Sales Cube Dataset

1.2. Configuring the timeframe covered by the Dataset.

2. Scheduled data synchronization of Sales Cube Dataset.

Sales Cube Dataset delivers the following multiple tables that support sales data to Power BI.

Detailed description of the tables, all columns and their content is available in additional document called Sales Cube Data Dictionary.

Technical Requirements (non-functional)

Influence on standard functionality of Magento Platform

Provided solution should not influence the performance of the frontend of the Magento Platform. In case if all configuration and layout is cached, there should be no more than 0.01 seconds delay on the frontend. Such delay may be caused by increased amount of files that needs to be processed by the autoloader.

On the administration pages of the Magento Platform delay on the core pages should be no more than 0.1 seconds.

Each page of the extension related functionality should not open longer than 5 seconds. Exceptions might be requests that trigger some actions on the backend that needs to be performed.

Scheduled Data Processing

Data processing by the scheduler cannot be done in parallel. Only one instance of the cronjob responsible for data processing is supported. Running the scheduler from different Magento Instances connected to a single database is not supported and may result in errors.

Proper functionality of the Schedule should ensure jobs to be executed in the correct order and within the appropriate time.

Sales Cube Data processing time

Data processing includes data extraction, data transformation and data load to Power BI. Time it takes to perform each step of data processing may vary depending on amount of data. But certain qualifications should still be guaranteed:

1. Extension should not trigger queries in Magento Database that takes longer than 1 minute to execute.
2. Extension should not call queries in the DWH database that execute longer than 10 minutes.

The requirements listed in this section are not guaranteed if overall Magento database file takes more than 25 GB of disk space. However some attempt to decrease the processing time might be taken, success is not guaranteed.

Data Extraction Time

Initial data extraction as well as data extraction after Data Clean Up might take up to several days to finish. Consequent data update (iterative extraction) should not take more than 1 hour for handling up to 24 hours of data.

Data Transformations Time

Data transformation is taking data, extracted from Magento Platform and prepares the data model optimized for Power BI Service. This process might take up to 3 hours after initial data extraction and should not take more than 30 minutes after consecutive data updates.

Data Load to Power BI Service

Data Load speed is limited by Power BI Service and should not be slower than 1000000 rows per hour.

Response time

Some pages of the Extension perform multiple requests to Power BI Service to update the information that would be presented on the page. Therefore, maximum time it takes to load single page is set to 40 seconds.

Development and debugging capabilities

Some development-related functionality is provided as is and is not a part of a standard bug-free guarantee. Please refer to Users Guides to get more information about those pages.

Constraints (limitations) and Assumptions

There are certain limitations associated with Power BI API that put some boundaries on the usage of the extension.

Here is the list of known limitations of Power BI Service APIs:

• 75 max columns
• 75 max tables
• 10,000 max rows per single POST rows request
• 1,000,000 rows added per hour per dataset
• 5 max pending POST rows requests per dataset
• 120 POST rows requests per minute per dataset
• If table has 250,000 or more rows, 120 POST rows requests per hour per dataset
• 200,000 max rows stored per table in FIFO dataset
• 5,000,000 max rows stored per table in ‘none retention policy’ dataset
• 4,000 characters per value for string column in POST rows operation

Additionally there are limitations on the capacity depending on used Power BI subscription

• Max of 1 GB of storage for Power BI Free subscription
• Max of 10 GB of storage for Power BI Pro subscription
• Max of 100 GB of storage per Power BI Premium / Power BI Embedded instance

Free subscription does not support functionality that allows sharing Power BI Service content with colleagues.

Please, refer to official Power BI documentation from Microsoft for more recent information about the limitations of Power BI Service.

Limitations of Power BI Integration (Plus) Extension

Dataset with Table Profiles does not support incremental load. Therefore each time, data of a table is loaded to Power BI Service, this table gets truncated and new data is sent.

Amount of rows sent to a dataset within an hour should not go over 1 000 000 rows. For the Power BI Integration Extension, this means the following:

• Amount of rows in a dataset should not go over 1,000,000 rows.
• If the dataset is updated often than once per hour, limit of 1,000,000 rows should be divided by frequency of data actualizations per hour. Example: dataset updated each 10 minutes will be updated 6 times per hour, thus it should have no more than 1,000,000 / 6 = 166,666 rows.

Limitations of Power BI Sales Cube (Lite) Extension

Data of Sales Cube Dataset cannot be loaded faster than 1,000,000 rows per hour.

Total limit for the amount of rows per dataset is limited by Power BI Service: max 75 tables with 5,000,000 rows per each table.