14 Replies Latest reply on Dec 15, 2013 1:59 PM by Ryan Pham

    Create data extract from PowerPivot cube for packaged workbook

    Corey Meier

      I have a seen a few discussions regarding this topic but I have not been able to accomplish this.

       

      Ultimately, I have an SSAS online cube that I would like to get select data from and store within a Tableau Package workbook for distribution.  One approach I am taking is to create a PowerPivot file with defined MDX queries for just the data I need from the cube (not all the data within the SSAS whole cube).  But when I attached Tableau to this PowerPivot data connection, the connection looks like a cube and I am unable to perform a data extract.

       

      I do not have a lot of experience with PowerPivot but assuming that even though I have created tables of SSAS Cube data with PowerPivot, PowerPivot behaves like a local cube...correct?  If so, anyway to have the PowerPivot file be "offline"?

       

      Any suggestions?

        • 1. Re: Create data extract from PowerPivot cube for packaged workbook
          Russell Christopher

          Hi Corey -

           

          Even though PowerPivot data is "offline", Tableau doesn't really see it as such. To Tableau, PowerPivot is (more-or-less, anyway) an online SSAS cube. And as you've discovered, the result is that you can't create an extract as we don't support creating extracts against OLAP data sources.

           

          Regardless of how you approach this, I think you're going to need to distribute "one more file" - whether that file is a PowerPivot workbook, or an offline cube that you create with Excel against SSAS itself.

           

          I'd test whether you can create a packaged workbook against an offline cube (which you can create via Excel and other tools) or the PowerPivot workbook itself. I'm guessing you can't, or you wouldn't post the question to begin with!

           

          Assuming the idea above doesn't work, what about using PowerPivot to create a PivotTable report, and then exporting the PivotTable OUT of Excel as a CSV? Then, use the CSV as your data source and extract away...

          1 of 1 people found this helpful
          • 2. Re: Create data extract from PowerPivot cube for packaged workbook
            Corey Meier

            Russell,

             

            Thanks for your reply.  In regards to the "one more file" approach, which do you recommend?  As I have mentioned above, I am just starting to play with PowerPivot...but to access the PowerPivot workbook data sources, would I just connect Tableau data connection as an Excel File instead of a PowerPivot file?

             

            I will also investigate the ability for Excel to access SSAS cube via MDX commands...basically creating the data source within an Excel file...any suggestions on this approach as well?

             

            Thanks in advance for your help!

             

            Corey

            • 3. Re: Create data extract from PowerPivot cube for packaged workbook
              Russell Christopher

              You'd actually use Excel against PowerPivot or SSAS as a "poor man's Extract/Transform/Load" tool. When building a Pivot Table in Excel, there are options you can turn on to make it look and feel like a plain-old tabular list of rows  - for example, "Repeat All Item Labels" under "Report Layout" in the Design ribbon.

               

              After you've built this "Pivot Table which looks like a regular old list of rows", you'll Copy/Paste it into a new worksheet as a table. At this point, you can save the newly copied "real table" as an CSV. This CSV becomes "just one more file" that you connect directly to with Tableau and extract.

               

              Clear as mud?

              • 4. Re: Create data extract from PowerPivot cube for packaged workbook
                Corey Meier

                Russell,

                 

                Thank you once again for your help in dealing with this situation.  Based on your guidance, I have been successful with getting SSAS cube data into a data extract within a Packaged Workbook through the use of PowerPivot and Excel 2010!

                 

                The process I followed is simliar as you noted above with the exception of creating a separate CSV file of the data.  Here were my steps for future reference:

                 

                1. Within a new Excel file, create the PowerPivot table(s) based on the SSAS cube needs by defining the MDX queries of the data.

                2. Once the data sources are defined, within the PowerPivot window, select "Flattened Pivot Table" from the View menu.  This will then create a new worksheet within the Excel File.

                3. Within the new pivot table, select the PowerPivot Tables data elements to be included within the "Row Labels" section of the Excel pivot table.  Ultimately I created a "table" view within the Excel worksheet.

                4.  Then I saved the Excel file.

                5.  Using Tableau, I then connected to the Excel File data source (not PowerPivot driver) and selected the appropriate worksheet(s) into the Tableau Workbook.

                6.  Once in the Tableau workbook, I would follow the process of creating a Tableau Data Extract.

                 

                Ultimately my goal of getting SSAS cube data into a Tableau Data Extract has been accomplished!  The only downside is the process of refreshing Excel 2010 using the PowerPivot add-in where the PowerPivot data sources needs to be manually refreshed along with the Excel pivot tables as well...where ideally I would like that process to be automated the Excel file was opened.

                 

                Thanks once again for your help.

                 

                Corey

                • 5. Re: Create data extract from PowerPivot cube for packaged workbook
                  bill.smith.0

                  I have related issue when trying to connect Tableau to PowerPivot file and that is that Tableau cannot read the PoswrPivot file..  I have successfully used PowerPivot to select data from large Teradata database but then when attempting to get Tableau to read the related PowerPivot table I get a Tableau errror message that says Tableau cannot find the PowerPivot table.

                   

                  Any have the same or similar problem.

                   

                  Thanks for your time and comments.

                  • 6. Re: Create data extract from PowerPivot cube for packaged workbook
                    Russell Christopher

                    Bill -

                     

                    Make sure you have the latest version of Tableau Desktop and all the PowerPivot-related drivers & Add-ins out on tableausoftware.com/drivers . The current (2012) version of PowerPivot storage cannot be read by earlier versions of Desktop, etc - they can only read the "original" PowerPivot database format.

                    • 7. Re: Create data extract from PowerPivot cube for packaged workbook
                      Corey Meier

                      Bill,

                       

                      Are you trying to connect to the PowerPivot Excel file using the Microsoft PowerPivot connect driver from Tableau?  Through my initial trials of accessing the PowerPivot data, I found my connection error messages were due my version of PowerPivot and Analysis Services drives/add-in on my computer.

                       

                      The following article helped me determine what version of Tableau add-in and Microsoft driver was necessary to connect.

                      http://kb.tableausoftware.com/articles/knowledgebase/choosing-powerpivot-addin-and-driver

                       

                      Once I updated to the correct versions, I was success in connecting to PowerPivot file.

                       

                      Thanks.

                      • 8. Re: Create data extract from PowerPivot cube for packaged workbook
                        bill.smith.0

                        Thanks so much for the suggestions. I think I have all of the versions and drivers at the correct state however I certainly could have messed up. In any event I went back and redid the driver updates from the Tableau Driver web site:

                         

                        Tableau 7.0 add-in for PwoerPivot Excel

                        MS SQL Server 2012 PowerPivot Excel

                         

                        Still get error message whan I ask Tableau to get the Excel PowerPivot data. I must be doing something wrong, I even tryed to get Tableau to get data from Excel PowerPivot data that I created from another Excel spreadsheet instead of Teradata.

                         

                        Thanks again

                        • 9. Re: Create data extract from PowerPivot cube for packaged workbook
                          Russell Christopher

                          Let's step back - Here's a PowerPivot workbook that is just loading some trivial data from SQL Server.

                           

                          I have the following drivers / add-ins loaded:

                           

                          ScreenHunter_02 Nov. 21 18.12.gif

                          ScreenHunter_03 Nov. 21 18.12.gif

                           

                          Using this stuff, I can connect to the Vertipaq data stored in the attached workbook. Can you?

                          • 10. Re: Create data extract from PowerPivot cube for packaged workbook
                            Ryan Pham

                            Hi Corey,

                             

                            I tried the iSeries Access ODBC driver to access the HTE AS400 database, but I have not been successful.  I wonder if your provided work around instructions still work?


                            • 11. Re: Create data extract from PowerPivot cube for packaged workbook
                              Corey Meier

                              Ryan,

                               

                              I have not worked with the iSeries Access ODBC driver...so I am not sure if my work around to getting SSAS Cube data will work with your situation.

                              • 12. Re: Create data extract from PowerPivot cube for packaged workbook
                                Jude Hoffner

                                Hi Russell,

                                 

                                I'm in trial with Tableau 8.0 and a major part of what I'm exploring is connectivity to PowerPivot workbooks on my desktop (no server/sharepoint/etc configuration. I'm just a solo guy). I was able to connect to your sample workbook, but the Sum Balance measure is delivering null values to my Tableau worksheet. I would expect to see the same values that we see in the pivot table in your powerpivot workbook. What am I doing wrong? Feels SO close to awesome but not quite there.

                                 

                                Thanks.

                                Jude

                                • 13. Re: Create data extract from PowerPivot cube for packaged workbook
                                  Corey Meier

                                  All,


                                  I have recently resumed my need to export data from an online SSAS Cube.  The original downfall with my process defined above was that that PowerPivot needed to be manaully refreshed.  I have since then located an application that can be scheduled and updates both Excel and PowerPivot!  Take a look at Excel and PowerPivot Refresh Suite - Home.

                                   

                                  Thanks.

                                  Corey

                                  • 14. Re: Create data extract from PowerPivot cube for packaged workbook
                                    Ryan Pham

                                    HI Corey,

                                     

                                    Your original process is automated with this new tool?  Is it possible if you can email me at ryan.pham@stocktongov.com with step by step screenshots of your new process so I can model after it?

                                     

                                    The process I followed is simliar as you noted above with the exception of creating a separate CSV file of the data.  Here were my steps for future reference:

                                     

                                    1. Within a new Excel file, create the PowerPivot table(s) based on the SSAS cube needs by defining the MDX queries of the data.

                                    2. Once the data sources are defined, within the PowerPivot window, select "Flattened Pivot Table" from the View menu.  This will then create a new worksheet within the Excel File.

                                    3. Within the new pivot table, select the PowerPivot Tables data elements to be included within the "Row Labels" section of the Excel pivot table.  Ultimately I created a "table" view within the Excel worksheet.

                                    4.  Then I saved the Excel file.

                                    5.  Using Tableau, I then connected to the Excel File data source (not PowerPivot driver) and selected the appropriate worksheet(s) into the Tableau Workbook.

                                    6.  Once in the Tableau workbook, I would follow the process of creating a Tableau Data Extract.

                                     

                                    Ultimately my goal of getting SSAS cube data into a Tableau Data Extract has been accomplished!  The only downside is the process of refreshing Excel 2010 using the PowerPivot add-in where the PowerPivot data sources needs to be manually refreshed along with the Excel pivot tables as well...where ideally I would like that process to be automated the Excel file was opened.