5 Replies Latest reply on Dec 30, 2013 8:36 PM by malcolm stanley

    Loading date dimension into Tableau

    Egor Ushakov

      A couple of months ago I had a little experience of building document in QlikView. It was really easy to make a "classic" loading date dimension into QlikView document with a script to get "internal" date dimension table similar to that on attached screenshot. Is it possible to execute similar operation inside Tableau or building such a dimension in external data source is the only option?

        • 1. Re: Loading date dimension into Tableau
          Russell Christopher

          Hi Egor -

           

          Generally, people don't use these old-school date dimension tables at all in Tableau. When I see someone creating a data model in Tableau and importing one, the first thing they nearly always do is literally hide 95% of the fields in the table, only leaving 1-2 (Date, DateKey) visible.

           

          The reason for this as you've probably noticed is that we can automatically create any number of dateparts from a date with a single click. We can also do things like set the start of calendar, (CY or FY, first day of week, etc) with a single click.

           

          Users find this much more intuitive than having to choose a different field from a table depending on whether they want to see info broken down by Year/Quarter/Month/Week/Minute/Whatever.  This is one of Tableau's "claims to fame", in fact.

           

          Most fields in "classic" date dimensions go virtually unused. Users stick with basic, bread and butter fields like "Month", "Week", "Hour", "Year", etc.  I think the only real value that an old-school date dimension brings to Tableau these days is recording holidays - sometimes that can be quite helpful based on what sort of analysis you're doing.

           

          So to answer your question - "No". We don't generate date dimension tables because we take a completely different approach to the "date problem". If you explore the Tableau data models created by some of the largest and most sophisticated Enterprise customers, you generally won't see these.  For good or bad, ours approach is much more user-centric.

           

          Hope this helps!

          • 2. Re: Loading date dimension into Tableau
            Egor Ushakov

            Russell,

             

            First of all, thanks for your comprehensive reply!

             

            I think I should decipher a little my real needs hidden in loading date dimension inquiry.

             

            Here’s real life use case. Sales Reps perform two types of Activity – Meetings associated with Contacts (individuals) and Meetings associated with Accounts (legal entities). Source database keeps information about these Meetings in two different table sets. It’s essential that information collected by Sales Reps about these two types of Meetings differs significantly. There is no problem to analyze each activity separately. But to analyze whole Sales Reps performance I had to build additional database view that UNIONize data for Meetings of both types. If I need to add one more activity type I should rebuild UNIONizing view once more. Even add three more types of activity still not a much work to do. But I would like to use “old-school” date dimension (even in its shortest form with (Date, DateKey) columns) to use it like a thread to string a “beads” of different activities on it. In fact I need to replicate this technique just using master Date dimension instead of Product one given in Knowledge Base article.

             

            I tried to use data source for one type of activity as Primary and the other as Secondary. But faced with some issues. Moreover there are minor issues while using filters from secondary data source (see example discussion here ). At last it's obvious that date ranges of activities may not overlap completely. In my case it’s not a big deal to load unionized data into Tableau second time. But this is not elegant and what if my today datasets increase dramatically?

            Thus I think I do need master Date dimension to be able to play with them together in one Tableau view.

             

            Or may be there are any other techniques to meet mentioned use case requirements?

            • 3. Re: Loading date dimension into Tableau
              Russell Christopher

              Hey Egor -

               

              Now I see - I think you're taking exactly the right approach here - you need a conformed dimension of some sort (in this case date) to knit your two tables together via Blending (or can you use a JOIN right in the database?).

               

              As I mentioned, we don't offer a lot of tools around building a true date dimension table, since we generally don't use 'em. I'd just grab a script off the net to populate one of these suckers in your database of choice, then pull it into Tableau. Not perfect, but not too sloppy, either.

              • 4. Re: Loading date dimension into Tableau
                Egor Ushakov

                Thank you, Russell, for approving my approach!

                • 5. Re: Loading date dimension into Tableau
                  malcolm stanley

                  so I have a similar challenge: I have two databases with server data showing various aspects of a deployment. The individual rows are date and time stamped, but at random intervals. The content fields of the databases are not joinable, and the timestamp fields will not join cleanly as they are non-continuous.

                   

                  What I hear you saying is that I need to create a separate table that contains only timestamps, which I can use to join the other two tables. So to clarify, I need to create a table with ~365 * 24 * 60 * 60 = 31,536,000 rows PER YEAR so I can get second-level resolution on my reporting.

                   

                  You should perhaps add a date/time sink to your feature set so this sort of jury rigging is avoidable.