Power BI vs QuickSight

Announced last year Amazon QuickSight catch attention of companies and users. Amazon QuickSight is a business analytics service from Amazon. As BI solution Amazon QuickSight allows users to build visualizations, perform ad hoc analysis, and quickly get business insights from data.

 A comparing of QuickSight with Power BI from Microsoft -a Leader in the Gartner Magic Quadrant for Business Intelligence and Analytics Platforms helps to understand better a value of solution from Amazon.

Sign up

Users can sign up and get started fast and free as with Power BI as with Amazon QuickSight.

Data Sources, Data Modeling, Data Relationships

The list of Amazon Quick Sight connects to data is much modestly then the list of Power BI data sources. Users can upload CSV or Excel files, ingest data from AWS data sources such as Amazon Redshift, Amazon RDS, Amazon Aurora, and Amazon S3, connect to databases like SQL Server, MySQL, and PostgreSQL, in the cloud or on-premises or connect to SaaS applications like Salesforce.

To compare a speed of speed of loading data into both system we created the identical report in both system. We chose the table in Azure SQL Server database as data source. The table contains more than 10 Million rows. SPICE is a parallel, in-memory, columnar calculation engine from Amazon automatically replicates data for high availability. After a uploading process was started user has access to data and can work with it.

To access to database, we need to allow access from Amazon QuickSight IP Address range. Open Firewall settings on Azure portal to perform this operation.

User can copy data to the in-memory engine or directly query it with SQL.

The SPICE import speed is really impressive. 10 Gb dataset was imported to SPICE for 23 minutes.

To connect to the Azure SQL Server database from the Power BI users need to download the Power BI desktop application, previously the data source was available thru the Power Bi site.

In Power BI desktop user also can choose between two options: import or direct query to data.

10 Gb dataset was imported to Power BI for 25 minutes. When we set a data source we can easily add tables from data source.

In QuickSight after data loading user can manage data and select table.

The current version of Amazon QuickSight has limited data preparation capabilities. User can manage data fields, change data types and create calculated fields, filter data.

A big disadvantage in my view is that Amazon QuickSight doesn’t have ability to combine data from different data sources in contradistinction to Power BI. Amazon suggest to use Amazon S3 or Amazon Redshift to combine data sources and then import into QuickSight.

In Power BI user can choose from more than 50 data sources and combine any of these in one report.

User can easily format data: change data type, set category, sorting options, add a new column or a new measure, create a group,hide/unhide column, delete/copy/rename column.

More options available thru Query Editor- user can manage parameters, split columns, remove duplicates, replace values, group, merge or append queries, reverse rows, transpose. User also can manipulate with data using DAX language.

The list of functions that available from Amazon QucikSight for grouping data is included only basic function as count, sum, average, max, min. The list of functions that available when user set calculation field also very small.

Data Visualizations

Amazon QucikSight provides only basic chart types as Bar, Line, Pie charts, Pivot table, Scatter Plot and Tree Map. Amazon QucikSight doesn’t have any map visuals to work with geographical data. It is extremely low number of charts in compare with Power BI with rich variety of charts types and ability to create and load custom visuals from power bi web site.

Amazon QucikSight chart display control properties are extremely limited. User can show/hide legend, refine axis start/end points, customize chart colors and titles. User can not copy, past charts, change colors and backgrounds colors, set pictures.

On the moment of working on the article tool type and drill down features doesn’t work.

Charts on the Amazon QucikSight report page are not interactive. User can not just select pies of pie-chart for example and filter others chart as we can do in Power BI.

Amazon QuickSight includes a built-in suggestion engine that provides recommended visualizations based on the properties of the underlying data. Power BI has also Quick Insights feature available thru web site.

Mobile versions

Both Quicksight and Power BI have a native mobile application for iOS,Android to view dashboards.

Conclusions

The Amazon QuickSight data engine works great but other functionality is just on the very first step on the way to be used for creating serious BI Solutions. Most interesting features of the Amazon QuickSight  are still not available for users. Features are mentioned in the technical documentation still not in live as connecting to an ODBC data source, Embedding visualization into another website,APIs, Row level security.  In the QucikSight via Power BI fight Power BI is the undisputed leader for now. Let’s see how situation will change in this year.

 

 

 

Choosing a BI Solution for Your Organization

 

In the “Big data” era when even small organizations now have hundreds of gigabytes of data and business users want to use mobile devices for their BI needs, we can observe an evolution of visualization tools and analytical tools. Many IT giants presented their own products.

Tableau has been sole leader on the BI market for many years, but situation has started changing in 2016. The BI tool from Microsoft called “Power BI” is quickly catching up with Tableau. The Power BI is a business analytics service provided by Microsoft, which is integrated into the Microsoft Office 365 Suite is close to becoming the number one BI tool in the market.

Big data market’s giants like Amazon and Google also offer BI solutions for their clients.

“QuickSight” is the business analytics service in the Cloud from Amazon providing data visualization and BI analytics on a data from a range of sources, mostly within the Amazon cloud space. It comes with its own calculation engine, known as SPICE, which is essentially the data store where all calculations on the data are performed before being displayed.

Google Analytics Data Studio offers free and enterprise analytics tools to measure website, app, data to gain customer insights.

How to choose the BI product for organization? This is the question often asked by our clients. The comparison of four BI products by main categories such as data loading, data modeling, report preparation and distribution, and security helps us  find the answer to the question.

DATA SOURCES, DATA BLENDING AND MANIPULATION

After data objectives for analysis were determined, data was collected by client, and preliminary data cleaning processes were completed, the very important step is the data source analysis and data loading into BI system.

There is the list of the claimed supported data sources:

Sources : https://support.google.com/360suite/datastudio/topic/6370347,http://docs.aws.amazon.com/quicksight/latest/user/supported-data-sources.html,https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-data-sources/, https://www.tableau.com/products/techspecs#professional

Microsoft Power BI offers the greatest variety of data sources. The number of Data Sources included in the Desktop Version of Power BI covers most of those that Tableau, QuickSight or Google Studio offer. Besides access to databases, in the online version of the Power BI we can even find options to get data from websites, services and files. The list of services includes such unusual data sources as Adobe Analytics, Acumaica, Bing, Circuit ID, Datawiz Analytics, GitHub, Google Analytics, Insightly, JIRA, MailChimp, Salesforce, Scoop, Stripe and more. Also in the online version, Power BI has a quick analysis tool called “Quick Insights” that analyzes a specific data set, returning valuable insights using Machine Learning.

Both the Amazon QuickSight and Google Data Studio offer less number of data sources that Power BI and Tableau. The connections can be used to work with Amazon or Google products respectively.

The list of data sources that Google Data Studio offers is included CSV files, MySQL database,PostgreSQL database and Google services and products.

Both the Power BI and Tableau support a large list of on-premises data sources. The Power BI connects to on-premises data sources using the On-premises gateway. The gateway helps make the behind-the-scenes communication from a user in the cloud to your on-premises data source and back to the cloud quick and secure. For Tableau, we need to set the Tableau Online sync client to push extract refreshes to Tableau Online.

The Amazon QuickSight can connect to an Amazon EC2 or on-premises databases like SQL Server, MySQL and PostgreSQL, user just needs to add the Amazon QuickSight IP range to the authorized list in a hosted database.

The list of the claimed supported on-premises data sources:

For both Power BI and Tableau, users can choose from the two options: “live connection” or “import data”. For Amazon QuickSight users have to choose between the “SPICE” and the“Query” options. If user choose the “SPICE” option the data is importing into SPICE instead of using a direct query to the database (the “Query” option). In Amazon QuickSight the live connection option is available for all supported data sources.

The list of the claimed supported data sources for live connections:

The capability of data manipulation after dataset was loaded varies depending on product.

QuickSight and Google Data Studio provide simple data manipulation such as: change the data type, rename columns, set calculation field and filters. Data types supported  are String, Date, Integer or Decimal. Some uncommon data types such as Money for example are presented only in Power BI.

Tableau and Power BI have more capability to manipulate data such as set sorting,  group creation, data splitting, unions, creating dimensions and converting them to  measures and vice versa, data hierarchy, setting category such as geospatial.

DATA MODELING

Power BI, Tableau and QuickSight provide the data modelling options such as creating complex relationships between tables, adding columns and rows, importing from one table to the other, joining, filtering and much more. Power BI, Tableau and Google Analytics Data Studio provide the option to combine several different data sources in one report, QuickSight doesn’t have this feature.

In the past, the supporters of Tableau often pointed at the limitations of the Power BI such as limited number of data points that can be shown on some charts, complexity of using DAX or no forecasting features, complexity of excluding data points or category one-by-one, complexity of grouping data on fly. But the Power BI team treated it as a feedback and in 2016 implemented of features that did not exist in the Power BI but existed in Tableau.

At this point, QuickSight and Google Analytics Data Studio lacking usability and data modeling options of Power BI and Tableau.

DATA REFRESH

All products have a feature to refresh dataset manually or set schedule for an automatic update in dataset settings.

DOWNLOAD AND INSTALLATION

Power BI Desktop is available for Windows only, while online version is available for any platforms. Tableau Desktop version is available for both Windows and Mac OS with the same functionality. Amazon QuickSight and Google Analytics Data Studio don’t provide a desktop version. 

IMPLEMENTATION

Implementation depends on many factors such as the number of data sources, company size and the number of users.

Tableau provides a variety of implementation and consulting services. For enterprise-level deployment, there’s a four-step process spanning weeks, and for smaller-scale deployments, there are quick-start options that can complete setup in a matter of hours.

Microsoft Power BI is a cloud-based service that’s simple to implement.

Getting started with Amazon QuickSight is simple and fast.

Google Analytics provides the Solutions Gallery that contains in-product solutions (such as dashboards, custom reports and segments).

CUSTOMIZATION

The Power BI allows users to import into the system the custom visualizations. Users can create the visuals  themselves or download them from the Power BI Visuals Library.

QuickSight, Tableau and Google Analytics Data Studio don’t provide a way of loading  custom visualizations.

REPORTS DISTRIBUTION

Users can share their reports in Power BI by publishing them to the Power BI Service for free. Some content and connections only available for Pro users, however these reports cannot be shared with Free license users. Also, a report can be published on web and anybody with the link can see it. Also, a report can be embedded into the site or application. The new Power BI Pro feature allows user to set  email subscriptions for reports. It allows users to receive emails with reports that matter most to them. Once users are subscribed, Power BI will regularly send screenshots of that report page directly to user’s inbox whenever the data changes.

Tableau and Power BI users can export data as Excel spreadsheets, Tableau supports exporting dashboards as PDFs or shared via Tableau server, Power BI supports exporting to PowerPoint or embedded in SharePoint Online. Also, Tableau dashboards can be published and shared via Tableau server (required a separate license).

In all systems when users share analyses, dashboard users be able to select the recipients (email address, username or group name), permission levels, and other options before sharing the content with others.

With Google Analytics Data Studio users can share report or give an access by link.

SECURITY

As for user authentication, Power BI uses Azure Active Directory to authenticate users who login to the Power BI service. User who connect to the service and has access to reports and can share reports may be not authenticated against the original data sources. Also, user can be restricted to update and share report. The full access to report and dashboard on Power BI site can be organized through publishing in the group. All group members have full access to the reports and dashboards in the group.

Power BI also provides row-level security: users who have restrictions on certain rows of the data will see the filtered data.

For data security Tableau allows authors to control whether users should be prompted for credentials upon accessing an online version of the report if report  using live connection. Also in Tableau, users have to select from the pre-set user roles such as viewer, interactor, editor. Similar to Power BI, Tableau provides row-level security and allow authors of the report set up user filters.

In Tableau users can build the security system based on active directory groups using the synchronization Active Directory groups and Tableau server.

Amazon QuickSight doesn’t provide row-level security  and synchronization Active Directory groups. Amazon QuickSight users can have two roles: admin and user. Admin user can create and delete user accounts, purchase annual subscriptions and SPICE capacity in addition to using the service.

COST

This is probably the most important factor for companies, especially for small businesses when choosing solutions. The current price of Tableau is around $1000 for a perpetual license/user. Also with this license user can connect up to 6 data sources. The Professional license includes connection to up to 44 data sources. This does not include implementation costs.

The standard version of Power BI costs from $0 to $9.99 month/user. That is $120 year/user.

To get started with Amazon Quicksight user can get 1 user license and 1GB of SPICE capacity for free.

Amazon Quicksight Standard Edition will cost $12 per user per month with no usage commitment and $0.25 / gigabyte / month for SPICE storage (beyond 10 gigabytes). Enterprise Edition will cost $24 per user per month with no usage commitment and$0.38 / gigabyte / month for SPICE storage (beyond 10 gigabytes).

Google offers the Google Analytics Data Studio for free. For larger enterprises Google offers the Google Analytics 360. Pricing starts at $150,000 / year. The minimum subscription period is one year.

 

SERVICE UPDATES

While Tableau updates its product once or twice a year, Power Bi launches an update on a monthly basis. On top of that, Power BI selects new features to include in its updates based on requests made by end. Amazon and Google don’t provide an information about a frequency of updates.

COMMUNITY SUPPORT

The Power BI user community is growing( http://community.powerbi.com/). Microsoft Power BI User Voice (https://ideas.powerbi.com)  allows users and teams to create powerful and prioritize feature requests to drive strategic product decisions. Users can find documentation and videos on Power BI site and Power BI’s channel on YouTube.

The Tableau community includes more than 100 000 users. Users can ask and answer questions in the Forums or/and  join a local user group. https://www.tableau.com/community

The largest Google Analytics user community unites more than 150 000 users. https://plus.google.com/communities/114481059214254340537

The AWS blogs about the QuickSight can be find by the links: https://aws.amazon.com/it/blogs/aws/category/amazon-quicksight/
https://quicksight.aws/

MOBILE ACCESS

Tableau, Power BI, Google Analytics Data Studio, QuickBase have applications for the Android, iPhone and tablet support. Users can access your data anywhere, explore reports and dashboards.

The Power BI has also the application for Windows Phone.

CONCLUSION

Microsoft and Tableau are two of three BI software vendors who are listed as leaders in Gartner’s 2016 Magic Quadrant for Business Intelligence and Analytics Platforms.

Tableau is still the more popular BI tool on the market, but Microsoft has made impressive advances in Power BI to narrow the gap since its inception in 2014. The continuous efforts made by Microsoft to improve the tool have elevated its quality and it is currently a very appealing product with a fast-growing community. Microsoft releases new updates to Power BI on a frequent schedule to improve system functionality and enhance user experience. Also, Microsoft has made progress in building its user community. The integration of Power BI as one of the available Office 365 apps, enables it as a cloud-based analytics solution that is easy for users to create, view and share reports across organizations.

Amazon QuickSight is a fast and easy cloud-powered business analytics service with which users can build visualizations, perform ad-hoc analysis, and quickly get business insights from their data. The service is offered for good price but the list of available data sources and amount of provided features considerably more modest than those provided by Power BI and Tableau.

Google Analytics is tool from Google that built to work mostly with Google’s data and products so users can use your digital analytics insights to drive real impact. The tool offers much less capabilities and features in comparison with Tableau and Power BI, but can be of interest for users who uses Google products already. Google Analytics 360 (non-free version) with the BigQuery features can process large amount of data in seconds and  helps to get insights in fast and easy way.

 

Implementation of Power BI Row Level Security (RLS)

Implementation of Power BI Row Level Security (RLS) when connecting to On-Premises databases from the cloud.

Row-level security enables controlling access to rows in a database table based on a user role. With new updates from PowerBI team we can now implement a logic when users from different roles/groups are limited to see only the data allowed to read by their role/group.

Previously, row-level security could be implemented in Power BI Service by leveraging Analysis Services On-Premises, based on user with USERNAME function of DAX. Now Microsoft added the RLS support to cloud models.

Let’s look at how to implement Row-level security on PBI dataset level and how to directly connect to Oracle on premises from the Power BI service in the cloud.

For demo purposes we created the following three users and two groups in Office 365 AD:

  • Bill Smith – global sales manager(USA) can see orders all of customers.

“PBI Demo 11” group

  • Justin Trudeau – regional manager(Canada) can see orders customers from Canada only.

“PBI Demo 14” group

  • Klaus Schmidt – regional manager(Germany) can see orders customers from Germany only.

“PBI Demo 14” group

Figure 1: Users Location

 As data source we choose the well-known “Northwind” database.

Figure 2: Tables relationship schema

Power BI Service is connected to the database through the Enterprise Getaway.

Figure 3: Data Flow schema

Next, we created a simple sales report in the Power BI desktop and publish it in “PBI Demo 11” group. Each Office 365 groups is shown as workspace in Power BI Service.

Figure 4: Publishing to Power BI

Let’s return back to our demo users and see how to configure RLS for them. User Bill Smith can view the report on Power BI website and he has a permission to apply row level security.

Figure 5: The Sales Report

We pined a live page to the new “Sales Geography” dashboard.

Figure 6: Pinning the Sales Report to a new dashboard

On Datasets tab we opened the dataset and chose security tab.

Figure 7: Security tab

In Row-Level Security section we added two new Roles: “Germany Managers”,” Canada Managers”.

We added Justin Trudeu user to the “Canada Manager“ role members.

Figure 8: Row-Level Security section

Now we create a new rule for  “Canada Manager” role. In rule expression we can use DAX expressions and any data tables from the dataset. We created very simple DAX expression for our demo: country field from Customer table is filtered by value of Country column.

Figure 9: Row-Level Security section

The same operation we made for Klaus Schmidt user from “Germany Manager” role.

In our demo use in the “Global sales manager” role can share the dashboard with regionally managers and view all orders for all customers.

Figure 10: The dashboard sharing

When Klaus Schmidt user logs in in PBI Service, he can view orders for customers from Germany only.

Figure 11: The regional manager dashboard

Figure 12: The global sales manager and regional manager dashboards

Conclusion

As Power BI evolves, we see more and more features helping users to provide more sophisticated reporting. Row level security feature, which was until recently only available through Microsoft Analysis Services, now available through native Power BI means, allowing connection to other databases, such as Oracle. Being released as a new feature, it is still based on familiar tools, such as DAX queries, while providing convenience of configuring RLS from within a web browser.