4 Replies Latest reply on Feb 11, 2013 11:19 AM by kurt keating

    Blending Data SSAS and Excel

    kurt keating
      Hello Tableau!
      I am trying to blend data from SSAS and Excel.   We have a cube, yet it is lacking any true GEO coding.   So I exported customer names and zip codes from the relational database and put it into excel.

      When I try to blend the data using 'customer name' it seems to work in a normal table report, however trying to plot revenue by customer on a map leveraging the blended zip code results in 2 errors (and it removes the customer name relationship link between datasources):
      1) Analysis Services database error 0x00000001: The '1024' locale is not supported.
      2) Analysis Services database error 0x80004005: Query (3, 2) The dimension '[Latitude]' was not found in the cube when the string, [Latitude], was parsed.
      And here is the query it is performing:
      {[Measures].[Revenue], Latitude, Longitude}
      NON EMPTY [Engagement].[Customer Name].[Customer Name].AllMembers DIMENSION PROPERTIES [MEMBER_UNIQUE_NAME],[MEMBER_CAPTION] ON ROWS
      FROM [PRIMARYDataSource]
      As far as specifics (Windows 2008 R2 Server with SQL Server 2008 R2, Tableau 8.0 beta)
      Anyone have any insight here?  I'm trying to prove out how I can supplement CUBE data with flat files and this was a disheartening first step...
        • 1. Re: Blending Data SSAS and Excel
          Russell Christopher

          Hey Kurt


          This sounds like one for support, frankly.


          The first error is SSAS complaining that it doesn't support the LocaleID it's been sent (1024 - which isn't tied to a country or language but means "no proofing"). Normally values like 1033 (US - English), 1036 (FR - French) are sent and are used by SQL Analysis Services to do localization/translation. It makes no sense that 1024 would get in there.


          It appears that our auto-generated lat and lon fields are being included in the query to SSAS, which doesn't make a whole lot of sense, either.

          • 2. Re: Blending Data SSAS and Excel
            Russell Christopher

            This was interesting to me, so I just tried to reproduce the same behavior you're seeing. I'm not getting your error messages, but I see what's going on.


            The situation has to do with the Latitude and Longitude fields you need generated from your Excel (Customer / Zipcode) file. You don't need this Lat / Lon information in your "Table Report", so things work.


            You obviously do need these in a map, and that's where your problem is.


            In the screenshot below, notice that my cube (Direct Sales - with a BLUE check mark) is my primary data source. Cubes must always be the primary data source when you use them for data blending purposes.


            Note that my excel file is the secondary (orange check mark) source. They key thing here is that the Latitude/Longitude measures for the Excel data source are dark grey (disabled):


            ScreenHunter_03 Feb. 10 12.52.gif


            This is Tableau essentially telling us "Sorry, I don't geocode information in the secondary data source".


            I bet what you did is drag in the Latitude / Longitude fields from the CUBE data source as I have above. We need this information generated from the Excel information (zipcodes) , not the cube (customer names)...so the values for Latitude/Longitude will be NULL coming from the cube.


            I've made this apparent in the screenshot above by choosing to show NULL geocoded values as 0,0 Lat/Lon - so we're plotting sales from the cube over "Null Island" at 0,0.


            You might still want support to take a look at this, but I'm afraid you have happened to hit a combination of "this is how things work in Tableau" that make your specific scenario really tough:


            • Cubes must be the primary data source when blending (otherwise you could use your Excel source and get the Lat\Lon that you need)
            • We do not auto-generate Lat\Lon information on secondary data sources
            • 3. Re: Blending Data SSAS and Excel
              kurt keating

              Thanks for your help!  I noticed that it was using the Lat/Lon in the CUBE query, which I DID NOT expect after reading how Tableau blends data.  

              One thing to note is that I didn't actually drag and drop those fields into my query.   I simply chose one metric from my primary source (the cube) and one from my excel sheet, which was Zip Code.  This enabled the map report option.  Clicking on the Map format 'automatically' selects the Lat/Long. 


              However In my case it pulled from the secondary source. Here is a screen shot after clicking the Map format showing the lat/lon with the orange check marks:


              A few things about this screen shot.  I put Raw Revenue as my metric and selected zip code as my rows.   That was it.  However, in order to get zip code to appear on my report, I had to go into the secondary datasource and click on the 'Link' icon to re-link the data.  I had already made this relationship in the edit relationship dialog box.


              If you notice it then adds customer to my report (as if it needs the 'linked' field to be present in the report) and it puts the Lat/Lon in the report for me, but they are from the secondary datasource!  I have NO ability to drag and drop these as they are greyed out.  


              The primary datasource Lat/Lon are available to drag and drop but the format is by customer which is not desired.  Removing the customer field from the report puts me back in the position of having to re-link the data because it destroys the link between customer name (which is the relationship link between the cube and excel).


              Very confusing, however I can open a support ticket.    Are you saying in your post that it is potentially a better solution to ADD lat/lon to my excel worksheet in order to use it in the graph?


              thanks again!


              • 4. Re: Blending Data SSAS and Excel
                kurt keating



                I took my excel sheet and added the Lat/Lon as fields mapped to the zip code.


                Hopefully this is on the right track as it seems to work: