1 2 Previous Next 16 Replies Latest reply on Jul 27, 2016 8:21 AM by Joe Oppelt

    Null values--connecting an excel sheet to an ODBC iseries connection

    Jillian Lellis

      Hi,

      I'm pretty new to tableau but I have done some training online. I am trying to do data blending from an odbc connection to an excel sheet. They both have a COUNTY field that is formatted the same. I am getting only null values. Help please! I am attaching the dummy data.

       

      thanks,

      Jill

       

      BTW- I have been able to get the joins to work when the data is exported from our system to the same excel workbook on a different tab but the goal is to have the store codes be able to update if we ever update the code table without having to do the export then join that way.

        • 2. Re: Null values--connecting an excel sheet to an ODBC iseries connection
          Jillian Lellis

          Thats how i connected Excel. We were told to not go the DB2 route, but to use the odbc iseries connection. Is this a problem?

          • 3. Re: Null values--connecting an excel sheet to an ODBC iseries connection
            Toby Erkson

            We need details.  Take your time and explain.

            What is the reason behind not using the native connector? 

            Yes, ODBC connectors typically have less functionality and can be slower than the native connectors.

            • 4. Re: Null values--connecting an excel sheet to an ODBC iseries connection
              Jillian Lellis

              Our IT does not want us connecting to our DB2 system any other way. In the past, rather than having a read-only connection someone overrode data using a different connection. Now our IT does not want us to connect any way other than the odbc iseries connection in read-only. It is a mandate from our IT group and marketing has no say over this. If I could make a case to connect another way, maybe this could change but for now we're stuck.

              • 5. Re: Null values--connecting an excel sheet to an ODBC iseries connection
                Toby Erkson

                Ah, okay.  Your IT folks are uneducated about Tableau.  Tableau is read-only (bullet point A).  Also, Tableau can customize the native connection if they need to make tweaks for the environment (bullet point B).

                 

                For your IT dept to read:

                1. Is Tableau a read only connection?
                2. Here's what I wrote up for our environment to enforce the isolation level as mandated by our DBAs:  IBM DB2 Isolation Level Setup .  This is used on our Tableau Servers and every author who uses DB2 is supposed to have it on their computer.
                  1. This may be of some help to them regarding the customization of the IBM DB2 connector and is what I referenced for above when I tackled this problem:  Customizing and Tuning ODBC Connections | Tableau Software

                 

                I'm not a DB2 expert but it's part of our major data sources.  There is not a valid reason I can think of to not allow the native connector for Tableau Desktop and Server.

                • 6. Re: Null values--connecting an excel sheet to an ODBC iseries connection
                  Jillian Lellis

                  So without connecting that way, the data blending capabilities of an odbc and excel are not going to cut it? That's very disappointing. I guess i could just export the data then put both on a separate tab in excel but that totally defeats the purpose of what the goal is in having it connect to our system in the first place. Shoot.

                  • 7. Re: Null values--connecting an excel sheet to an ODBC iseries connection
                    Toby Erkson

                    Jillian, I'm only coming at this from the data source side of things.  I don't know the exact ODBC connector you're using but if it's basically a generic one then it may be an issue.  It would still be best to have a Tableau Desktop person look at your workbook to see if there is something that can be done.  Maybe Shawn Wallwork or Joe Oppelt can weigh in with their expertise.

                     

                    Also, get your IT folks to talk with your Tableau representative so they can be informed about Tableau and get the proper drivers installed so you can efficiently do your job.  Without you and others to consume "their" data they wouldn't have a job -- you are one of their customers, period.

                    • 8. Re: Null values--connecting an excel sheet to an ODBC iseries connection
                      Shawn Wallwork

                      Toby, thank for the ping. But I'm not too excited about finding and installing drivers I don't need (and having troubles finding) just to open a workbook. Maybe Joe Oppelt will be up to the challenge.

                       

                      Cheers,

                       

                      --Shawn

                      • 9. Re: Null values--connecting an excel sheet to an ODBC iseries connection
                        Jonathan Drummey

                        [jumping in on this one because data blending is a favorite subject of mine]

                         

                        Hi Jillian,

                         

                        If you're having problems with data blending the problem is highly unlikely to be in the database driver, it's much more likely to be in the data itself. The reason why is that data blending occurs after aggregation (i.e. when Tableau queries each source), so if you can connect to each source and from each build a worksheet that shows the County then data blending will almost certainly work. The challenge is the data because the linking dimension values in each source have to *exactly* match for data blending to work. The two biggest things to check for are:

                         

                        - trailing and leading spaces in the County field in each source

                        - capitalization for the County field in each source

                         

                        For example the County field in the Excel data is all upper-case and at first glance appears to have no trailing nor leading spaces, so the first one is ANDERSON. If the County field in your iSeries source has values like Anderson then data blending will fail. The good news is that you don't have to make the change in your raw data, you can create a calculated field like UPPER([County]) and then use that as the linking dimension and/or do manual cleanup using Tableau's aliases feature. Data blends are done *after* record level calculations such as the UPPER([County]) and aliases are assigned.

                         

                        All of that said, you are much more likely to get better performance and have access to a fuller range of Tableau functions if you use a native driver, so Toby's post is a good one to use with your IT folks.

                         

                        Finally, assuming the problem is the data the workbook that you sent is not useful for us to actually diagnose the problem because the iseries data depends on a live connection to the DSN (data source) that is configured on your machine and Tableau Packaged workbooks (twbx's) do not include the data for live connections to server-based data sources. For us to see the data you'd need to extract that data and then export the packaged workbook.

                         

                        Jonathan

                        3 of 3 people found this helpful
                        • 10. Re: Null values--connecting an excel sheet to an ODBC iseries connection
                          Joe Oppelt

                          Well I'm sure glad Jonathan jumped in.  As far as I am concerned, it's a black-box assumption that Tableau joins up whatever data it pulls from Primary Source and Secondary Source for me, regardless of the type of source it originally was.

                           

                          So when I saw ODBC and potential problems specific to that, I just shrugged my shoulders and moved on.

                          • 11. Re: Null values--connecting an excel sheet to an ODBC iseries connection
                            Jillian Lellis

                            well poo. The capitalization is a non-issue as both are formatted as all-caps. I am unsure, however, about the leading or trailing zeros. When I export and do a vlookup (old school, i know), I get them to match so I'm thinking there are none, but I'm unsure. I am so new to this so I really appreciate your help.

                            • 12. Re: Null values--connecting an excel sheet to an ODBC iseries connection
                              Toby Erkson

                              Master JD, thank you so much for jumping in!

                               

                              Note to self:  Jonathan is into "data S&M"

                              • 13. Re: Null values--connecting an excel sheet to an ODBC iseries connection
                                Jonathan Drummey

                                Hi Jillian,

                                 

                                I suggest you try the following, this is what I do when testing and validating blends:

                                 

                                1) In Tableau validate that County is available as a linking dimension from the Excel to the iSeries source and vice-versa when you use Data->Edit Relationships.

                                 

                                2) In each data source check the County field’s data type by right-clicking on the field and choosing Describe… This step is here because Tableau data blending is sensitive to date types for numbers (whole/integer vs. decimal/floating-point/real) and dates vs. date times. I’ve never heard of this being an issue for strings/text but it’s worth checking at least to know what the fields are being read in as. If the data types are different then you’ll need to change one of the fields to be the same as the other. For example if Shipping Date is a date time in one source and a date in the other then in one of the sources you’ll need to right-click on the field and choose Change date type to set the appropriate type.

                                     Special note: MS Access has a Lookup Table feature where a field like County could actually store a number (like a unique County ID) but the the lookup points to a Counties table where there’s a record for each County ID with an associated County field that is the text string that is displayed. In MS Access we’d only see the text string for each county, however the Microsoft JET driver that Tableau uses to connect to MS Access sources would see that County ID number and the data type of the field would be a number, not the text string, and data blends from sources that have County as a string would fail. To work around this we’d need to edit the data connection for the MS Access data to use a join from our table of interest to the Counties table or build a query in Access that is used as the data source. See http://drawingwithnumbers.artisart.org/i-have-wee-data-microsoft-access-and-tableau/ for other considerations when using MS Access as a data source.

                                 

                                3) Create a new worksheet with the Excel source as primary (it’ll have the blue checkmark icon) and County from the Excel source on Rows.

                                 

                                4) Create a new worksheet with the iSeries source as primary and County from the iSeries source on Rows.

                                 

                                5) In the worksheet you set up in #3 add County from the Excel source to Rows to the right of the original County from the iSeries source. Validate that County is turned on as a linking dimension in the Excel source (i.e. it has the orange chain icon to the right of it). Then visually scan down the values. If you get all Nulls or some Nulls for values for the County from the Excel source where you would expect to have matching values then do the following:

                                     a) Compare the values in the iSeries worksheet to the Excel worksheet.

                                     b) Compare the values in the original Excel file with the original iSeries data

                                 

                                6) In the worksheet you set up in #2 add County from the iSeries source to Rows to the right of the original County from the Excel source. Validate that County is turned on as a linking dimension in the iSeries source. Then visually scan down the values. If you have problems then you’ll need to do the same steps as in #4.

                                 

                                7) If you’re still having problems then create a Tableau data extract of the iSeries data and review the worksheets you created in steps 3-6. If this works while the live connection to the iSeries data does not then the issue is likely to be some issue with the driver or potentially a data interaction (such as the data type issue I mentioned in #2).

                                 

                                8) If you’re still not able to get it to work (or need help with the above steps) then send me an email at jonathan(dot)drummey(at)gmail and we can set up a screenshare.

                                 

                                Jonathan

                                • 14. Re: Null values--connecting an excel sheet to an ODBC iseries connection
                                  Jonathan Drummey

                                   

                                   

                                  In my head what I'm into is something like Gandalf's stand at the bridge in the mines of Moria where he's facing a seemingly hopeless battle and still determined to fight, except instead of "You shall not pass!" I'm thinking something like "We will find a way."

                                   

                                  The other image in my head is one I put into my presentation on the attributes of the Tableau Zen Masters So You Want to be a (Tableau) Zen Master, here it is:

                                   

                                  Screen Shot 2016-07-26 at 7.27.03 PM.png

                                   

                                  Whatever way we choose to look at it my goals are to help other users get their needs met and learn along the way.

                                   

                                  Jonathan

                                  1 2 Previous Next