Eric Xue's Consulting Musings

BI Analytics - Microsoft Azure vs Hadoop Products

brokensnow@hotmail.com — Tue, 10 Jan 2017 07:22:00 +0500

During a latest client presale meeting, the client was asking for a mapping between "Microsoft Azure components Vs Hadoop Products" within Data, BI and Analytics space.

Then we put together the following information and hope it would be useful:

Microsoft Product	Hadoop/Open Source Software Product
Office365/Excel	OpenOffice/Calc
DocumentDB	MongoDB, HBase, Cassandra
SQL Database	SQLite, MySQL, PostgreSQL, MariaDB
Azure Data Lake Analytics/YARN	None
Azure VM/IaaS	OpenStack
Blob Storage	HDFS, Ceph (Note: These are distributed file systems and Blob storage is not distributed)
Azure HBase	Apache HBase (Azure HBase is a service wrapped around Apache HBase), Apache Trafodion
Event Hub	Apache Kafka
Azure Stream Analytics	Apache Storm, Apache Spark, Twitter Heron
Power BI	Apache Zeppelin, Apache Jupyter, Airbnb Caravel, Kibana
HDInsight	Hortonworks (pay), Cloudera (pay), MapR (pay)
Azure ML	Apache Mahout, Apache Spark MLib
Microsoft R Open	R
SQL Data Warehouse	Apache Hive, Apache Drill, Presto
IoT Hub	Apache NiFi
Azure Data Factory	Apache Falcon, Airbnb Airflow
Azure Data Lake Storage/WebHDFS	HDFS Ozone
Azure Analysis Services/SSAS	Apache Kylin, AtScale (pay)
SQL Server Reporting Services	None
Hadoop Indexes	Jethro Data (pay)
Azure Data Catalog	Apache Atlas
PolyBase	Apache Drill
Azure Search	Apache Solr, Apache ElasticSearch (Azure Search build on ES)
Others	Apache Flink, Apache Ambari, Apache Ranger, Apache Knox

Microsoft SQL Server 2016 GA is officially made available!

brokensnow@hotmail.com — Thu, 02 Jun 2016 03:31:00 +0500

So excited to announce that the MS SQL Server 2016 GA is just officially made available since last night!

Needless to say that this is the biggest leap forward in the Microsoft data platform history with real-time operational analytics, much better Data Viz on mobile/web devices, built-in advanced analytics, improved security capability & all-in-one hybrid cloud solutions.

Well, not going to go through those new shinny features again here as I have already blogged 4 articles about MS SQL Server 2016 in past 18 months, and therefore, just quickly highlight a few key selling points for pre-sale purpose:

Performance:
New SQL Server boasts 100x faster queries, 30x faster throughput and 30x faster operations on its database server. The speed improvements can be achieved without having to upgrade the current hardware.

Security:
Row-level security, Dynamic data masking, Always end-to-end encryption as well as the enhanced TDE (Transparent data encryption).

Analytics:
Data warehousing and big data analytics is also a big focus in SQL Server 2016 through its built-in R support. This database can also store and query both structured and unstructured data.

Scalability:
The full editions of SQL Server 2016 will allow the database to scale up to 12 TB of memory and 640 logical processors with Windows Server 2016. Advanced analytics can be processed directly within the database thereby reducing latencies and operational costs.

Data Viz:
Much intuitive and visually-appealing SSRS standard pixel-perfect reports as well as mobile friendly reporting capabilities.

SSRS 2016 reporting still has its native integration with SharePoint 2016 as part of the new Power BI Dashboard portal.

Lastly, as for the new simplified MS SQL Server commercial licensing, the BI edition is no longer available and there are only Enterprise & Standard commercial licensing left ;)

Tableau Server Now Available on AWS Marketplace

brokensnow@hotmail.com — Thu, 07 May 2015 14:10:00 +0500

So excited that Tableau has joined the Amazon Web Services Marketplace with an option to quickly deploy Tableau Server in the cloud.

What this means to our Tableau customers in comparison to the traditional manual installations are:

AWS pre-config deployment for Tableau Server can save a lot time & money on the configuration and service provision fronts. (e.g. just imagine how much time can be saved when it comes to the configuration the tableau server public IP configurations...)
AWS pre-config installation for Tableau Server can set up the Demo/Production environment faster if the underlying data sources are already made available in the AWS Cloud Environment (such as SSAS cubes hosted on the same/separated AWS instances)
Lastly, the security configurations (e.g. ports/roles) will be easily taken care by the AWS pre-config set-up, rather than the traditional approach to go through the Tableau Server Admin console to manually configure those individual setting, then re-booting the server repeatedly in order to make sure that all the setting has been made effectively.

Happy Tableau-ing!!

All Best,

Eric

SQL Server 2016 public preview coming soon!

brokensnow@hotmail.com — Tue, 05 May 2015 17:14:00 +0500

So excited about this ground-breaking SQL Server 2016 public preview release in the coming months. In summary, the top capabilities for the release include:

Always Encrypted - a new capability that protects data at rest and in motion,
Stretch Database - new technology that lets you dynamically stretch your warm and cold transactional data to Microsoft Azure,
Enhancements Real-time Operational Analytics & In-Memory OLTP - leading in-memory technologies for real-time analytics on top of breakthrough transactional performance.
Built-in Advanced Analytics, PolyBase and Mobile BI with R integration

Also additional capabilities in SQL Server 2016 include:

Additional security enhancements for Row-level Security and Dynamic Data Masking to round out our security investments with Always Encrypted.
Improvements to AlwaysOn for more robust availability and disaster recovery with multiple synchronous replicas and secondary load balancing.
Native JSON support to offer better performance and support for your many types of your data.
SQL Server Enterprise Information Management (EIM) tools and Analysis Services get an upgrade in performance, usability and scalability.
Faster hybrid backups, high availability and disaster recovery scenarios to backup and restore your on-premises databases to Azure and place your SQL Server AlwaysOn secondaries in Azure.

The further details for the SQL Server 2016 public preview information can be found via http://www.microsoft.com/en-us/server-cloud/products/sql-server-2016/

P.S. the latest MS Power BI preview will be also fully released this summer as well along with much more appealing visualization and sophisticated data capabilities.

All Best,
Eric

Tableau 9.0 is on the way!

brokensnow@hotmail.com — Tue, 03 Feb 2015 17:26:00 +0500

As you may be aware, the major version releases of Tableau Desktop 9.0 and Server 9.0 are on the way for public downloading in the coming days.

As for this new release of the Tableau Server 9.0, you will get the following major upgrades:

New Intuitive User Interface
Better Security Permissioning
Extended Command Line and REST API client integration
High Availability by new Cluster Management
Performance Enhancements by Server Processor Cores & Server Caching

The future details can be viewed via http://interworks.co.uk/blog/whats-new-tableau-server-9-0/

With this new release of the Tableau Desktop 9.0, the new stellar key features are:

More and more data connections (Yes, it can easily connect to IBM SPSS and SAS data files directly at your fingertip now!)
More fluid Web 3.0 User Interface
New stunning Mapping graphics (Even outshine the classic Tableau maps)
New shining responsive Tooltips
More sophisticated In-line Editing capabilities along with much more powerful Calculated Field Editor.

Please feel free to view the details via

http://interworks.co.uk/tableau/tableau-9-0-brings-us-connections/

http://interworks.co.uk/kevin-pemberton/new-user-interface-enhancements-tableau-desktop-9/

Note: From my personal point of view here, PowerBI and Tableau seem to be serving for quite different purposes of tools to the business users/current markets as PowerBI is more powerful and flexible for the reporting capabilities whereas Tableau is just more of a quick graphical dashboard-ing tool for the data discovery purpose.

Happy Tableau-ing!

Eric

Tableau and R Integration

brokensnow@hotmail.com — Wed, 23 Jul 2014 15:06:00 +0500

Hi All,

Have been tasked to put together a quick write-up about Tableau and R integration for the IM Dashboarding solution based upon the Tableau latest 8.2 releases.

Please see my research details as follows and more than welcome for any feedback from all of you:

As many people might know that Tableau just released the latest 8.2 version to the global market with a bunch of new exciting features (http://www.tableausoftware.com/new-features/8.2), do you also know that the Tableau also supports R Integration since version 8.1 on the Desktop & Server tools (Not on Tableau Reader yet).

Benefits for using R

As an open-source almost 2.5 million user base analytics platform, R provides a wide variety of analytic analysis goodies here – it includes that statistical tests, linear and nonlinear modeling, time-series analysis, classification and clustering.

There are many benefits by leveraging R into the Tableau toolkits and please see the key points as follows:

· It’s free and open-source backed by the community and millions professionals.

· The free functions and rich features can be created all the time by the domain experts and other industrial professionals and the quality of the libraries will be assured by the community and experts.

· Enrich Tableau with a growing collection of statistical analysis and data mining libraries to help them gain deeper insights from their data.

Integration with R

Getting R set up with Tableau Desktop/Server requires R and Rserve installed and configured.

· R can be simply grabbed from http://cran.us.r-project.org/

· Rserve is a package created from Tableau communities in order to connect to the Tableau applications

· Rserve required to be installed on the command line and got connected to the Tableau via “Rserve Connection” configuration Screen or the tabadmin command line on the Tableau Server environment

· R functions and models can now be used in Tableau by creating new calculated fields that dynamically invoke the R engine and pass values to R.

Common Question about Tableau + R Integration

Q: Can we use precompiled packages, models, and other things with Tableau and R?

A: Yes. The general rule is, if you can do it in R, you can easily integrate it with Tableau. This includes any statistical packages, parallel computing packages, models and libraries, whether they are standard within R or if you create them independently. This also includes commercialized versions of R, including Revolution Analytics. You can also return data frames from R one column at a time.

Q: When integrating Tableau and R, what is the best practice for debugging R scripts or discovering errors?

A: There are two ways to do this. The first is to use the 'write.csv' command within the calculated field that calls an R script. The other, is to use the debug version of the standalone executable of Rserve (Rserve_d.exe) which will print out any code that R is performing as Tableau calls the R scripts.

Q: Can you use R to reshape data?

A: Yes, see this example (http://www.tableausoftware.com/solutions/r-integration) where R and multidimensional scaling are used to reshape 1600+ columns into Tableau.

Q: Can Tableau pass data from a relational database to R?

A: Yes, Tableau can pass data from any source and run R scripts on that data, whether a flat-file, relational database, cube, or an unstructured data store.

Q: Can you dynamically pass various levels of drill-down dimensions to an R function?

A: Yes, and very easily too. In Tableau, R scripts are run in table calculations and therefore can be run against various dimensions. Simply change the aggregation level of the desired dimension and compute the table calculation accordingly.

Q: How do you run a function with a mix of variable types?

A: Tableau can send R mixed types of data. In SCRIPT_X, the X represents the variable type that is returned to Tableau. In most cases, the one column returned to Tableau will contain a single variable type. If there are mixed types (e.g. mixed number and text values), it can be returned as a string using SCRIPT_STR.

Q: What is the best practice for R Integration with Tableau that R models can be reused within the same session?

A: See this getting started whitepaper(http://www.tableausoftware.com/learn/whitepapers/using-r-and-tableau) about Rserve. It provides more details about saved sessions, and the difference between Windows and Linux installations of Rserve in private vs. shared environments.

Q: Does Tableau Reader integrate with R?

A : At this time, you will need Tableau Desktop or Tableau Server to view a Tableau workbook with R scripts.

Lastly, it’s also worth pointing out here that Tableau by default comes with its own built-in Analytics Models and capabilities, so please feel free to check out the following resources if you would like to explore:

http://www.tableausoftware.com/new-features/forecasting

http://www.tableausoftware.com/learn/webinars/using-analytics-be-predictive-and-proactive

Hope you would find it useful.

All Best,

Eric

Another cool Data Visualization example

brokensnow@hotmail.com — Tue, 08 Jul 2014 12:08:00 +0500

Hi all,

Just quickly sharing this cool Data Visualization example here -> Hacker live attacks visualization http://map.ipviking.com and hope everybody would enjoy it!

All Best,
Eric

A quick Intro about the Microsoft Power BI & product comparison among Power BI, QlikView & Tableau

brokensnow@hotmail.com — Thu, 03 Jul 2014 13:49:00 +0500

Hi all,

Have been tasked to put together a quick write-up with respect to the Microsoft Power BI in comparison to QlikView & Tableau for a potential larger BI solution piece for a well-respected client.

Just sharing my research with everyone here and more than welcome for any feedback from all of you:

An brief overview of Microsoft Power BI

Microsoft Power BI is a collection of different Excel add-ins, SQL Server services features, SharePoint and/or the Office 365 Cloud. Although desktop Power Pivot add-ins can be easily installed in Excel 2010 and is already embedded along with Power View in Excel 2013, Microsoft BI on-premise server set ups are complicated, one week or longer installations and configurations of SharePoint, SQL Server and Reporting Services that also require Active Directory accounts and Kerberos for authentication.

(Note: Power View is both embedded in Excel 2013 but also as a stand-alone version via Reporting Services Power View reports hosted in SharePoint. Each version has a different mix of capabilities.

Great ETL & Data Cleansing/Validation Components

Microsoft Power Query is quite nice and has both basic and advanced self-service ETL capabilities including pivot/un-pivot, joining, filtering, de-duplicating, grouping, splitting, and transforms.

More advanced functionality can be achieved via Power Query M scripting. Although Power Query is in preview and a v 1.0 offering, it gets monthly enhancements and already has proven to be a great tool for personal data collection and shaping.

Not fully compatible for Tablet/Mobile devices yet

Microsoft Power View was designed in Silverlight. The Office 365 Power BI Cloud version of Power View has an HTML5 option in preview for some of the chart types. On-premise Microsoft BI Power View is only Silverlight today and not fully mobile device friendly.

Data Visualization not as appealing as Tableau

Microsoft Power View has basic bar charts, line charts, pie charts, a scatter chart and non-customizable Bing Maps that can show pie charts or circles – no thematic mapping or overlays.

Power View is lacking combination charts, dual axis charts and many other common chart types. Power View does not allow granular control of visualizations, axes, colors, labels, annotations, drill actions, conditional formatting or visualization display logic, visual banding, statistical reference lines or custom marks.

There is not an API for automatically building Power View visualizations. Only Power View scatter charts in can play over time periods. Power View does not support easily viewing underlying details or exporting the view data. You can do some simple drill drowns. There is no comparable export of Power View detail data shown on a view. To see the raw underlying detail data, you need go back to the back-end Power Pivot model.

Power View does not have features for changing data color variations, KPI icons are only circles, no chart object sizing, contouring and no customization of tool tips. Power View does have text sizing and limited chart labeling options.

Product Comparison

Here are just some insights based upon the BI/Analytics communities practice and be categorized into the following aspects: Business Criteria, Data Visualization Criteria and Technical Criteria.

As mentioned before, it’s just another product comparison based upon people personal view over those products according to their own preferences and practices experience.

Business Criteria	Tableau	QlikView	Microsoft / Power BI - SQL Server 2014/SharePoint 2013	Comment
Time to implement	Fast	Longer	Longer
Scalability	Good	RAM Limited	Excellent	Tableau: virtual RAM
Enterprise Ready	Good for small organizations who can use the cloud option.	Good for medium businesses	Good for SMB	Qlikview is more mature but Microsoft has a much clearer vision than previously. Tableau is demonstrably used in many large organizations, as their customer list shows.
Long-term viability	Fastest growth	Public company	Dependable	Excel is widely used in the organization so no adoption is required
Getting free online help?	Tableau forums	Qlikview LinkedIn group	Microsoft online communities	Tableau and Microsoft provide great free online help. Qlikview has its own forum which you sign up for. Tableau has the best free training videos I've ever seen.
Getting paid training	Yes	Yes	Yes	The costs vary depending on the courses.
Big Data Support	Above Average	Average	yes, ODBC connectivity to HDInsight	It is on all of their roadmaps. Tableau offers a bewildering number of ways to connect to lots of data sources. However, they don't connect to PDW very well so Microsoft wins for PDW support. It isn't clear if QlikView support PDW or not.
Partner Network	Tableau: 1000+ partners	Qlikview: 1000+ partners	A lot more partners

Visualization Criteria	Tableau	Qlikview	Microsoft / Power BI - SQL Server 2014/SharePoint 2013	Comment
Eye Candy' Appear	Yes	medium	Medium	Tableau blows users away with its beautiful data visualization.
Data Interactivity	Excellent	Excellent	Excellent	Tableau's interactivity has improved a lot and hits the mark for a lot of requirements. The scripting requirement in QlikView makes me a bit wary for users. Microsoft's various reporting tools need to be aligned more.
Visual Drilldown	Excellent	Excellent	Very Good	Power BI Excel has a neat drill down feature.
Offline Viewer	Free Tableau Reader	Personal Edition	Excel spreadsheets downloaded	Tableau and Power BI offer Excel downloads for offline viewing.
Analyst's Desktop	Tableau Pro	Qlikview Desktop	Excel	Excel is familiar within the organization.
Dashboard Support	Good	Excellent	Excellent	Dashboarding methodologies can be implemented in QlikView and Tableau. Tableau has basic default KPIs but these can be manufactured easily enough. QlikView seem to be popular with finance departments and seem to talk well with them.
Web Client	Very Good	Very Good	Very Good	No real distinguishing factor here. Microsoft has Excel services and we know that business users love Excel!
Mobile Clients	Excellent	Excellent	Good	Tableau and Qlikview have an edge on Microsoft for now, but the release of PowerBI for O365 is visibly getting traction and interest in the Preview.
Visual Controls	Very Good	Very Good	Very Good

Technical Criteria	Tableau	Qlikview	Microsoft / Power BI - SQL Server 2014/SharePoint 2013	Comment
Data Integration	Excellent	Very Good	Very Good	Tableau integrates easily with Google Analytics for further analysis, but this is not required at the early stages of a BI strategy. You can get extra connectors for QlikView from DataRoket
Development	Tableau Pro	Qlikview Developer	SQL Server Business Intelligence or Excel skills	QlikView has scripting, which the organization will need to learn. This may incur training costs. If the organization already has strong SQL Server BI Developer skills in-house, and would not require further training.
64-bit in-RAM DB	Good	Excellent	Excellent	SQL Server 'talks' to other systems and will output data easily to QlikView and other formats. This is not reciprocated i.e. once the data is in QlikView, it stays in QlikView.
Mapping support	Excellent	Average	Excellent	Tableau has Mapping. Excel 2013 has 3D Power Map as a feature within it, and this is interesting for further, future analyses.
Local data files (text, spreadsheet etc.)	Yes	Yes	Yes
Relational databases (SQLServer, Oracle etc.)	Yes	Yes	Yes
OLAP cubes (SSAS, Essbase etc.)	Yes	No	Yes	As for SQL Server 2014 & 2012 SP1 CPU4 onward, the PowerView can directly connect to both tabular and multi-dimensional BI Semantic Model and before SQL Server 2012 SP1 CPU4, the PowerView could only connect to tabular semantic models.
Online data sources	Yes	Yes	Yes	Microsoft's new Power Query allows you to search online and scrape datasets straight into Excel.
Multi-source access	Yes	Yes	Yes
Multi-table access	Yes	Yes	Yes
Extracted data storage	Optional (proprietary)	Proprietary	Data remains where it is.
Maximum capacity	Unlimited	Billions of rows
In-memory engine	Desktop or Server	Desktop or Server		Tableau reads SSAS and PowerPivot Cubes, but not very well. Tableau and QlikView want to suck the data into their own data models; Microsoft keeps the data where it is, where it is easily accessible by Microsoft and other vendors.
Modeling, Analytics	Below Average	Below Average	Data mining and other capabilities	Microsoft is the winner here for providing a range of modelling and analytics tools such as Tabular model, SSAS. Again, the organisation has experience in these tools so you are leveraging in-house skill sets.
Data Mining	Limited	Limited	Yes	By 'Data Mining', I mean true data mining e.g. building neural nets with thought put into it about avoiding jitter, bootstrapping and so on. This is not 'what if' scenarios but data science.
Multidimensional	Very Good	Limited	Excellent	Microsoft is the winner for multidimensional modelling
xVelocity Support	Good	None	Excellent	Microsoft is a pioneer in xVelocity support
PowerPivot Support	Good	None	Excellent	Microsoft is a pioneer in xVelocity support
API	Excellent	Excellent	Excellent	Microsoft open their software up and have APIs available

Hope you would find it useful & any feedbacks are highly welcomed here!

All Best,

Eric

CodeProject

SharePoint 2010 Best Practices

brokensnow@hotmail.com — Tue, 10 Jul 2012 10:17:00 +0500

As a SharePoint consultant, chances are that the clients will always ask for the latest “SharePoint Best Practices” for business users and technical perspectives while I’m consulting on-site with them.

While I’m continuously evolving my own version of “SharePoint Best Practices” pack, I find that “SharePoint 2010 Best practice” from “Microsoft TechNet Wiki” can be extremely handy and resourceful. (http://social.technet.microsoft.com/wiki/contents/articles/8666.sharepoint-2010-best-practices-en-us.aspx)

The wiki is a growing article and I’ve subscribed to it so that I can stay in touch. There are already some excellent resources and I’ve learnt of several new sources that I’m going to follow up.

The Wiki has the following Table of Contents:

I bet that most SharePoint consultants will enjoy this as much as I do.

If you are interested in the interactive Mindjet version, alternatively you can download the SWF version of the file here: https://dl.dropbox.com/u/20530832/SharePoint%202010%20Best%20Practices.swf

SharePoint Governance Resource Centre

brokensnow@hotmail.com — Wed, 02 May 2012 11:39:00 +0500

CodeProject

As a SharePoint consultant, every once while the client we consult with will ask for a SharePoint Governance Plan documentation in order to control how an organization's Business divisions and IT teams cooperate to achieve business goals by leveraging the SharePoint platform.

This is an informative resource centre I normally reference to while documenting the governance plan -> http://www.letscollaborate.co.za/Resource-Centre/SitePages/SharePointGovernance.aspx

I'm more than happy to hear from you if you came across other white papers and documentations for your past and current SharePoint projects ;)