13 Replies Latest reply on Jan 18, 2017 12:06 PM by Shinichiro Murakami

    Question for "Alteryx" or good way to handle Cube(MS analysis services) data

    Shinichiro Murakami

      Hi Tableau experts

       

      I'm not sure it's appropriate to at this question at this place, then I can move to otehr place if there is recommendations.

       

      I read Bill's Tableau Ambassador Spotlight with Bill Lyons  and become interested in "Alteryx".

      I also heard from someone else about "Alteryx" that this is good tool to manage database.

       

      Our company's one of the reason to introduce Tableau was to replace Pro-clarity with Microsoft Analysis Services.

      As you know, handling this database's Cube data is really headache. But re-creating all the data connection to original datasource also requires ton of efforts.

       

      So, the question is that "Alteryx" can be the buffer(?) database connecting to Cube data, and connected from Tableau with calculation available.

      Or other input are also appreciated if you know the good way to handle MS analysis data.

       

      Thanks,

      Shin

        • 1. Re: Question for "Alteryx" or good way to handle Cube(MS analysis services) data
          Toby Erkson

          I'd recommend moving to the Forums as you'll have a greater potential for replies.

          • 2. Re: Question for "Alteryx" or good way to handle Cube(MS analysis services) data
            Chris McClellan

            Alteryx can be used to do a lot, but you can't connect Tableau directly to it.  The way I use Alteryx with Tableau is usually as an ETL, usually to modify data into a format that Tableau can easily use.  For me, this is usually writing the dataset to a database (SQL Server / Redshift / whatever) or a TDE (yes, still a database in the true sense).  I also have clients that use Alteryx to write to XLS because they prefer to use XLS in Tableau.  The bottom line is that it doesn't really matter what Alteryx writes to, as long as it's something that Tableau can read. 

             

            Today I found that I could use Alteryx to convert a CSV file to a SHP file which instantly solved a problem I was trying to solve while testing v.10.2 (thanks to Alan Eldridge though, because he thought of the solution)

            2 of 2 people found this helpful
            • 3. Re: Question for "Alteryx" or good way to handle Cube(MS analysis services) data
              Shinichiro Murakami

              Hi Chris

               

              Thank you for the quick reply.

              Then do you know : can Alteryx convert Cube data into new format which Tableau prefers?

               

              Thanks,

              Shin

              • 4. Re: Question for "Alteryx" or good way to handle Cube(MS analysis services) data
                Chris McClellan

                The Alteryx Community is as good as the Tableau Community

                 

                Solved: SSAS Cube Connection and MS Power Query - Alteryx Community

                 

                So, it can't connect natively, but there's a way to make it happen

                2 of 2 people found this helpful
                • 5. Re: Question for "Alteryx" or good way to handle Cube(MS analysis services) data
                  Shinichiro Murakami

                  Thank you Chirs !

                   

                  I keep this thread open for a while in a case any other input comes in.

                   

                  Thanks,

                  Shin

                  • 6. Re: Question for "Alteryx" or good way to handle Cube(MS analysis services) data
                    Shinichiro Murakami

                    Just FYI

                     

                    Q&A in "Alteryx" forum.

                     

                    Re: Good way to handle Cube(MS analysis services) ... - Alteryx Community

                     

                    Chris's  answer was much faster than the answer over there.

                    (Posted at same time)

                     

                    thanks,

                    Shin

                    • 7. Re: Question for "Alteryx" or good way to handle Cube(MS analysis services) data
                      Tom W

                      Is your company absolutely convinced it needs to continue with OLAP cubes? I started my career with a decent amount of OLAP development but I haven't done any in years and I haven't really found a solid need to implement any in a professional capacity thanks almost solely to Tableau. I find creating tables in a relational DB and loading that into Tableau as an extract provides me (and my users / clients) with all the performance I need.

                      • 8. Re: Question for "Alteryx" or good way to handle Cube(MS analysis services) data
                        Shinichiro Murakami

                        Tom,

                         

                        The fundamental solution should be re-creating the connection to other DB from Tableau directly, but I am looking for "easy way" (if there are) to use cube data temporally through Tableau + something.

                         

                        Thanks,

                        Shin

                        • 9. Re: Question for "Alteryx" or good way to handle Cube(MS analysis services) data
                          Yuriy Fal

                          Hi Shin,

                           

                          Talking a pure technology, you'd have a little chance

                          to apply your Tableau expertise when working with cubes.

                          Tableau is just Yet Another OLAP Browser (much like Excel or ProClarity).

                           

                          Of course it's easy to point Tableau to a cube datasource

                          and start slicing / dicing / drilling-down / rolling-up like a mad :-)

                           

                          BTW, having in-built Hierarchies in Dimensions --

                          with hierarchical filtering and drill-down / roll-up --

                          is a huge bonus of cubes, and the main pain point

                          when moving from cubes to flat tables / relational.

                           

                          You'd be able to calculate Gross Margin --

                          it's just Profit (Measure) divided by Sales (Measure) --

                          everything comes pre-aggregated in a cube.

                          You'd even do Table Calculations (on Measures only) --

                          as simply as that. But not more than that.

                           

                          Forget calculated dimensions altogether, don't even mention LODs.

                           

                          Forget Date / Time -- everything in Dimensions is just a "String" --

                          even if it looks like Date / Time (and has a hierarchy like Y-Q-M-D).

                           

                          Forget re-shaping data. And using a cube as a Secondary,

                          not to mention cross-DS join / filter.

                           

                          Forget Tableau Desktop for Mac (my personal red flag for cubes :-).

                           

                          BUT (and it is a BIG BUT :-) ... Your colleagues (and their jobs done)

                          may be heavily dependent on the data already in cubes.

                          They may be wired with the Excel-ProClarity habits, and that's not easy to change.

                           

                          From my own experience, doing something lateral -- 

                          which helps colleagues with their jobs -- is a win.

                           

                          Ask your MS SQL Server DBA for a direct connection

                          to the DSV (Data Source Views) which cubes are built on.

                           

                          Typically they are a kind of star-schema tables / views --

                          with Facts and Dimensions joined by Foreign Key - Primary Key.

                          These kind of data structures are the best to analyse directly.

                           

                          Do (nightly incrementally refreshed based on Date / Time) extracts --

                          if your MS SQL Server instance is over-loaded during working hours.

                           

                          And do apply your (great) Tableau skills to ship something not seen before.

                           

                          This would be a win-win for you & your colleagues.

                          That's my hope.

                           

                          Yours,

                          Yuri

                          2 of 2 people found this helpful
                          • 10. Re: Question for "Alteryx" or good way to handle Cube(MS analysis services) data
                            Shinichiro Murakami

                            Hi Yuri

                             

                            What a kind explanation

                            You always has good answer for difficult question.

                             

                            Anyways,

                            I read your comments and it seems like the technical key is below part.

                             

                            Ask your MS SQL Server DBA for a direct connection

                            to the DSV (Data Source Views) which cubes are built on.

                             

                            Typically they are a kind of star-schema tables / views --

                            with Facts and Dimensions joined by Foreign Key - Primary Key.

                            These kind of data structures are the best to analyse directly.

                             

                            I will try to talk to our MS SQL DBA guys.

                             

                            Thanks again,

                            Shin

                            • 11. Re: Question for "Alteryx" or good way to handle Cube(MS analysis services) data
                              Yuriy Fal
                              I will try to talk to our MS SQL DBA guys.

                              Don't push them hard, especially those who prepared cubes for ya.

                              You may well end up re-creating all cube calc logic in Tableau.

                              Let them have some bread'n'butter after all :-)

                              • 12. Re: Question for "Alteryx" or good way to handle Cube(MS analysis services) data
                                Jordan Carson

                                This was written by my colleague and I thought it would be worth mentioning here:

                                 

                                Apologies for a lengthy response, but I thought, if the data-source is an
                                SSAS database, there are a few things that we all need to be aware of. Probably
                                you already know about some or all of it, but listing them anyway just so it is
                                documented and available for reference for others.

                                 

                                All of the points below are based on my experience in working on Tableau on top of an SSAS database. Most of the limitations I highlight below were with Tableau v8 (hence the past-tense in my sentences below); I haven’t tried using an SSAS database with one of the recent Tableau releases, so not sure if it is better now. Probably worth trying as part of a small PoC to understand the behaviour and how easy/difficult it would be to go about building the dashboards
                                considering the requirements of our users.

                                 

                                • Tableau doesn’t allow data-extracts if the underlying
                                  data-source is an SSAS database i.e. Tableau supports only a live connection
                                  with an SSAS database.

                                 

                                • The performance of the Tableau report depends on the storage mode
                                  of the cube partitions in the SSAS database and the performance tuning done
                                  there –
                                  • MOLAP –
                                    • Stands for Multi-dimensional OLAP
                                    • Similar to the Tableau data-extracts i.e. when the cube is ‘processed’, the data from the underlying database is materialized based on the cube’s structure, aggregations defined and the hierarchies created on the dimensions.
                                    • This storage mode is
                                      generally used when the amount of data in the underlying database
                                      (data-warehouse) is huge and the users need optimum performance when using the
                                      cube.
                                    • Since the primary purpose of the cube as per my understanding is for data analysis and data-mining, having more data would help in performing those activities better and get more accurate results. With very high data volumes, MOLAP storage mode is used to deliver performance. Although that also has some downsides such as the data is not available in the cube in real-time i.e. as and when it changes in the underlying database, and the time taken by the cube to ‘process’.
                                    • ROLAP –
                                      • Stands for Relational OLAP
                                      • It is essentially a live connection from the SSAS database to its underlying data-source such as a
                                        SQL Server database.
                                      • This storage mode is used when the amount of data in the underlying database is comparatively less,
                                        or the performance tuning aspect can be applied at the database level.
                                      • So far, I have come across only 1 system from my previous clients where this storage mode was used,
                                        but there were performance issues so eventually migrated to MOLAP.
                                    • HOLAP –
                                      • Stands for Hybrid OLAP
                                        – a hybrid between MOLAP and ROLAP
                                      • I haven’t come across any data-warehouses that use this storage mode.

                                 

                                As you can make out from the above, MOLAP is best suited for performance.

                                 

                                In our case, since the SSAS database is under the ownership of a different team, we would be relying on them for this aspect and its tuning. There is a risk of running into a scenario where they may not make the changes requested by us if those changes would impact their other applications using that database.

                                 

                                • In certain cases, it is better to create such calculated members and sets in the SSAS database directly to help with the
                                  query performance, however, as mentioned above, we would be relying on the database team to do it for us. They may choose not to do it to avoid impacting the other users of the cube as they would also see those calculated members and
                                  sets if created directly in the SSAS database.

                                 

                                • Based on the rules to be used, it may not be straight-forward to apply row-level permissions to the data, however, I am
                                  hoping that we would be re-using the permissions model that is already applied to this data, in which case it may not be an issue. In case we plan to use a different permissions model with this data, it would be better to first consider its implementation before spending time on the other bits.

                                 

                                • In Tableau v8, there was a limitation that made it a bit difficult to implement some of the common Tableau functionalities. The limitation was that it was not possible to access a dimension in a ‘Calculated Field’ where we can access Tableau Parameters and that it was not possible to access a Tableau Parameter in a ‘Calculated Member’ where we can access a dimension. This perhaps was by design and the way SSAS and MDX queries work, however, it did pose some challenges in fulfilling certain requirements.

                                 

                                Functionalities such a parameter where the users can switch between flipper between different dimension fields was not straight-forward to implement and its work-around was resulting in a performance issue.

                                 

                                • Calculations where there was a need to check for a dimension member and calculate the measure accordingly, such as “if [Region] = Americas then [Profit1] else [Profit2] end”, was not straight-forward as the developer would have to write a ‘Calculated Member” to achieve it which is essentially the MDX expression that the cube understands. If the developer of the report isn’t familiar with the MDX expressions and the functions available to achieve the task, this proves to be very difficult thus impacting the dashboards / user-requirements and experience.

                                 

                                • It was not possible to create hierarchies within Tableau, however, they can be created in the SSAS database and are then available for use in Tableau.

                                 

                                • One nice feature is that when a hierarchy is used as a ‘quick filter’, it appears as a proper hierarchy i.e. one field with the
                                  expand/collapse behaviour for the hierarchy. While it is a nice feature that is currently not possible via a relational database (SQL Server for instance), it has its own downsides. One such downside was that the behaviour when filtered
                                  on multiple members across multiple levels on different parts of the hierarchy was erratic – sometimes it worked while sometimes it showed some misleading results.

                                 

                                • If a hierarchy was used on a view and the same
                                  hierarchy was also used as the quick filter on the dashboard, the drilldown
                                  functionality on the view (expanding the levels to open the lower levels of the
                                  hierarchy) resulted in auto selection/de-selection of the members in the filter
                                  corresponding to that hierarchy which in turn resulted in some unintentional
                                  filtering on the dashboard thus having a negative impact on the user
                                  experience.

                                 

                                • Another point around filtering but ‘action filter’ this time – if a target sheet on a dashboard was filtered by multiple action
                                  filters sourced from multiple other sheets where a different dimension was used as the field to be filtered upon, Tableau wasn’t always returning the right results. The behaviour, and thus the data on the target sheet, was different when tried multiple times, and there was no pattern in that behavior that helped in identifying the root-cause.

                                 

                                • In cases where the Tableau view needed to be a big table with more than 6-7 dimensions/attributes on rows/columns, Tableau generated a poor MDX query (or perhaps that was the only option – CROSS JOIN  across all those dimensions) that resulted in memory related issues on Tableau as well as the SSAS servers, so not very good for such type of views.

                                 

                                • It wasn’t possible to give an alias to a dimension member, which was/is very easy to accomplish with a relational data-source.

                                 

                                I had raised a few Support cases with Tableau when I faced those issues, and know for a fact that Tableau tried fixing some of those especially the ones around the hierarchy filter/action-filter but were unsuccessful. I hope they have been fixed in the recent releases.

                                 

                                In case I can think of any other challenges I had faced, I will let you know. In the meantime, it would also be good to go through the points documented by Tableau here.

                                 

                                I am happy to go through all of the above points to explain them in detail and to answer any questions that you may have.

                                 

                                I hope the above helps.

                                2 of 2 people found this helpful
                                • 13. Re: Question for "Alteryx" or good way to handle Cube(MS analysis services) data
                                  Shinichiro Murakami

                                  Jardan,

                                   

                                  Thank you for very detailed document.

                                  Give me some time go thru them

                                   

                                  Thanks again.

                                  Shin