One of the BI leaders tableau trial talk

Is the latest Gartner BI Magic Quadrant, in which one of the leaders of tableau behaves abnormally, with the executive Force Quadrant directly off the other products on a street, a forward-looking quadrant slightly beyond the MSBI, with unparalleled curiosity, specifically to the Tableau website Download the desktop Trial version of the experience, from a variety of details can feel the thick feelings, the product is really very humane, even the official website of the teaching video is a slightly broadcast cavity of Chinese voice, but in order to objectively analyze the user experience of this product, I did not follow the video to the system to learn tableau, just a little bit of their own projects often used in the analysis of the case to do some experiments, after all, is not a tableau development experts, so there is said wrong place also ask the expert to correct.

Open Tableau Desktop client, first need to connect to the data source, the supporting data source is very comprehensive, here choose SQL Server to do a ROLAP play, see, this is not the embodiment of the craftsman's feelings:)

I am connected to the data source is the local AW database, need to specify a few tables to be analyzed, I dragged past the dimproduct, DimCustomer, factinternetsales three tables, because the data source has a perfect primary foreign key relationship, so here is also correctly identified table relationship, In addition, the fuzzy query input boxes that are ubiquitous in tableau.

Take a look at the data source area on the left side of the workbook, Tableau does not differentiate dimensions and facts by physical tables, but rather by default according to the field type in the table, the number types used to aggregate totals are considered fact measures, other types such as text, dates, and primary foreign key keywords are considered dimensions. There are also some fields in itself that can be measured as a dimension, such as UnitPrice unit price, when you can drag and drop UnitPrice from the measurement area to the dimension area. Spit trough the data source tree menu did not find the "collapse all" function, to locate the specific field is troublesome, can only be used to find the field of the fuzzy input box, but also to hit the keyboard (Can you more lazy?). ）。

The content area in the middle of the workbook is much like a pivot table, and I drag and drop a productkey on the line, and I drag and drop a gender on the column.

The query script captured with Profiler is the following: a simple group by

By doing a one-click Row-and-column on tableau, or putting the ProductKey and gender dimensions on the row area, it is observed that tabular does not refresh the data from the data source, but that the client processing is done, which means that the tableau client caches the result set. Excel clients do not cache the result set, so Excel does a similar interaction by going to the data source to refresh the data, unless you manually specify deferred layout updates.

The pros and cons of Tableau are that they save the overhead of repeatedly loading data, and the downside is that if the data source data changes, it's not possible to notify Tableau clients that the cache expires, and I did an experiment to update the data source under the following script. Then the above interaction on tableau found that the data rendered by the Tableau client did not change, and the profiler did not capture any query commands until I had the correct data by appending a dimension to the row area, and Profilter also confirmed the data refresh. So tableau should at least have a manual trigger refresh function, not found this feature ... Does it mean that you can only deploy to Tableau server to trigger a refresh on a page?

Update FactInternetSales Set SalesAmount=SalesAmount+1

Toggle chart Type, this does not go to the data source refresh on both tableau and Excel, Excel charts are not as diverse and beautiful as tableau, but Tableau seems to be directly turning the pivot table into a perspective, unlike Excel's ability to display a pivot chart at the same time, And the charts can be linked to each other.

Add filters to the ProductKey, or to the naked eye, or one by one, Excel native function is the only way, and usually bu would like to be put into a bunch of SKU key (line break or comma interval), Excel can customize the function to improve the user experience, See the filter function in the Xpivot user manual http://www.cnblogs.com/xpivot/p/4317706.html,Tabular does not seem to have such extensibility

But still found a bright spot, according to the formula to specify the conditions to define some complex logic, I assume a requirement is: summary purchased SKU 214 or purchased sku217 member sales, note that the filter must choose Customerkey dimension

The generated script is a bit complicated, this is the problem that ROLAP is unavoidable, the SQL that the tool organizes does not expect the performance to be superior, even this kind of script expression is not much optimizes the space, unless in memory ROLAP, otherwise the larger amount of data will be very laborious. It is recommended to read this script to fully understand how ROLAP works

Note that the "aggregate purchase of SKU 214 or purchase of sku217 member sales" is not the same as the sales of SKUs 214 and 217, the subject is different (the former is the analyst, the latter is the analysis SKU), The results of the analysis are also completely different, the latter filter settings and query results, note that the filter replaced by ProductKey

We'll define the requirements, summarize the purchase of the SKU 214 and purchase the sku217 member sales, the script and the following, filter or Customerkey, you can see the data query results and the first two requirements are different

Max (IIF ([productkey]=214,1,0)) +max (IIF ([productkey]=217,1,0))=2

The above examples emphasize that the selection of the filter requires extra attention , a bit of inattention will be wrong, such as the same requirements, if you set the formula in the gender filter, you will find that can also be executed smoothly, but the data is completely wrong

Traced back to the script only to find that the original is using gender as the Subquery association field, BU certainly does not have the ability to check the check script, if not pay attention is very easy to make mistakes

I'm also trying to access the SSAS MOLAP data source with tableau, and Tableau's support for multidimensional databases is not enough to achieve these typical requirements analysis, or it may be limited by the MDX expression, which makes it difficult to translate visual settings into MDX script logic. Obviously the difficulty is far greater than the translation into SQL (PS: the degree of support for SSAS ROLAP should theoretically be better than MOLAP, which is not tested).

From the above, Tableau's visualization is powerful enough, at least these need cases are not in Excel itself, and I do it in Excel BI solutions by extending plug-in functionality, but unlike tableau, which requires coding, Because this coding method is not only a little higher threshold, but also easy to use wrong, not suitable for BU. In addition, some users experience less detail than Excel, find a feature is relatively laborious, Excel will often use the same functionality as a variety of shortcut portals to meet the different operating habits of users, but then again, if the subsequent version of Excel can also support tableau, the function of the filter defined by the formula, It's also very encouraging, at least it role BI staff will love it very much.

This trial is mainly to experience the flexibility of the filter, indeed there is a commendable design, after the opportunity to experience the legend of the powerful computing engine is how to work, will be similar to the vertipaq of the PowerPivot engine? Always think this kind of thing is like the difference between shoot camera and SLR, specific occasions can play an advantage, but related to the targeted tuning is very weak, not as good as the RDBMS tuning space, data from the RDBMS to the MOLAP engine has lost a lot of tuning capabilities, Can there be enough channels for other engines to flexibly solve performance problems? BI products in the propaganda, can always hear the exaggeration of the claim to be able to do real bu-driven seconds out of the report, deliberately ignoring a few prerequisites, one is to have a clean data source, in fact, the vast majority of business production libraries do not have such a precondition, If BU really go to the business production library to do real-time query, not to mention the second out, can do the query logic without flaws are very difficult things. As for performance, whether it can be done in seconds, depending on the hardware and data source tuning, In-memory ROLAP performance is comparable to MOLAP, if the company hard currency is not a time to add some soft power, you can first the RDBMS tuning, and then combined with MOLAP, Handling business issues that make both of them adept

One of the BI leaders tableau trial talk