14 Replies Latest reply on Nov 9, 2015 6:44 AM by Roxana Tudor

    How to make Tableau know a field is a date and not an attribute on a MS-AS Cube

    Andres Wagner

      Hi all,

       

      We are designing a cube on Microsoft Analysis Services and connecting Tableau to visualize the data.

       

      One of the dimension on the Cube is a Time dimension, with lots of attributes (week day, month, etc.) and the date field "as it is". However, when Tableau connects to the cube, the date field isn't recognized as that, it appears only as an additional field: the problem is that all the additional functionalities Tableau has to work with dates are lost.

       

      Any ideas?

       

      Many Thanks,

       

      Andrés

        • 1. Re: How to make Tableau know a field is a date and not an attribute on a MS-AS Cube
          Rob Mitchell

          This is a issue for us also so any insight would be appreciated.  I've noticed that cubes tend to be more difficult to work with in general within Tableau.  Has there been any thought to Cube specific training or tutorials?

           

          Thanks!

          Rob

          • 2. Re: How to make Tableau know a field is a date and not an attribute on a MS-AS Cube
            Austin Dahl

            Andrés,

             

            There is a way to do this, but you'll have to roll up your sleeves and do a little work.

             

            The basic idea will be to create a date measure that corresponds to the date dimension you are interested in.  By putting this measure where you want it, along with a level from the date dimension on the LOD shelf, you can do some nice things.

             

            In my example, I'm using an old Adventure Works cube which has a couple of different date dimensions on it.  I'll concentration on [Date].

             

            Steps:

            1. In the Schema viewer, click on the triangle and select Calculated Members...

              (screen shot)

             

            2. In the Calculated Members dialog box, click New

             

            3. Create the date measure

              - Name your calculated member,

              - Select the Measures dimension

              - Set result type to date

              - Set a high solve order

              (screen shot)

             

            4. Enter the formula for the date measure.

              For Analysis Services 2005, for the [Date].[Calendar] hierarchy the formula will be

             

              CDate([Date].[Calendar].CurrentMember.MemberValue)

             

              (screen shot)

             

            5. Create a view with the date field

              - Place the Date or Month generation of the [Date].[Calendar] heirarchy on the LOD shelf.

              - Put your new calculated measure on the Columns shelf.

              - Put another measure on the Rows shelf.

              (screen shot)

             

            6. Get fancy with trend lines and colors or date filters.

              (screen shot)

             

            In my experience, the CDate function only works with Dates and Months.  You can replace Months with Dates on the LOD shelf to drill down.  However, CDate doesn't work so well with quarters or years.  If you really need that, you'll have to get fancier with the calculation.

             

            I hope this helps.

             

             

            Rob,

             

            At last year's customer conference, there was a session on cubes, but there isn't one scheduled specifically for cubes this year.  There will be "Tableau Doctor" sessions that you can sign up for and get one-on-one problem solving help.  There is probably a good chance there will be enough cube users to do something less formal, a BOF meeting perhaps.

             

             

            Austin Dahl

            • 3. Re: How to make Tableau know a field is a date and not an attribute on a MS-AS Cube
              Rob Mitchell

              Austin, Thank you!  This was a huge help, it didn't solve our date issue specifically but that has to do with the awkward date structure being used on our end.  It did highlight a few other options we have which is going to be very beneficial. 

               

              If it's not too much trouble we had one more question.  One thing we've noticed is that trend lines aren't usually available to us when working with Cube data.  Can you point out what in your example caused the Trend lines to be enabled? 

               

              There are two of us coming to the conference from my university (American Public University) and we would love to get together, formally or informally, with a group to discuss cube related stuff.

               

              Thanks a ton for your help

              Rob

              • 4. Re: How to make Tableau know a field is a date and not an attribute on a MS-AS Cube
                Austin Dahl

                Rob,

                 

                The trick to getting trend lines is to have two continuous axes -- one for time; one for the measure you are trending.  When working with cubes it's a bit tricky to make a time axis continuous, since the cube itself defines discrete members like the year, quarter, month and day. In fact it's pretty tricky to make ANY cube dimension act like something continuous.

                 

                In my example above, in steps 1 through 4, I create a new measure.  That new measure is continuous and so that's the thing I put on the column shelf.  If you are using Analysis Services, creating a custom measure is the best way to get there.  You say your date structure is awkward, so it may too much of a stretch for CDate or the other VB functions that are available in Analysis Services.  I've tried a few with mixed success.

                 

                Another angle of attack copy and paste a subset of you data back into Tableau which will then treat it as relational data.  There's more flexibility on relational fields to change them from measures to dimensions and vise versa, but it still takes a bit of work.

                 

                First build yourself a view with the measure and your time dimension.  In the time dimension, drill down to the level that you are interested in.  Next select all the data and copy it.  Paste the data back into Tableau (assuming you have Tableau 5.0) and it creates a new data source based on the clipboard, but this time it is relational.  You then have to manipulate the schema to make your date files dimensional.  Finally you need to create a calculated field that reassembles the date from it's component parts.  One example, if your months are in strings would be

                 

                DATE([Month] + " " + STR([Day]) + ", " + STR([Year]) )

                 

                Then you can use that calculated field and your measure to do trend lines.  Overall it seems like this is just as tricky as creating a calculated member, but has less flexibility.

                 

                If you are using Essbase, you can do a similar trick to steps 1 through 4, but the formula will be different.  Also available in Essbase are so-called "Time Intelligence" dimensions.  If a dimension is created like that in the underlying cube, Tableau is able to create what is effectively a continuous dimension, and it shows up in the schema view inside the time dimension.  The Essbase Administration tools have a wizard for helping to create Time Intelligence dimensions.

                 

                I look forward to seeing you at the conference.

                 

                Austin

                • 5. Re: How to make Tableau know a field is a date and not an attribute on a MS-AS Cube
                  Austin Dahl

                  Rob,

                   

                  After chatting with you at the conference, you mentioned that you had Year, Month, Day on your cube and it was tricky to get the continuous date calculation.  I got a workbook from Julia in support with a lot of cube calculations.  It had a similar one from what I suggest above.  I was also able to create a calculation building up from component parts, like you described.  In the attached workbook, check out sheet 4. Another Continuous Date.

                   

                  The MDX I used was:

                   

                  Ancestor([Order Date].[Calendar Date].CurrentMember, [Order Date].[Calendar Date].[Year]).MemberValue

                  + "/"

                  + Ancestor([Order Date].[Calendar Date].CurrentMember, [Order Date].[Calendar Date].[Month]).MemberValue

                  + "/"

                  + [Order Date].[Calendar Date].CurrentMember.MemberValue

                   

                   

                  I made sure to set the type of the member to Date, put my Day field on LOD and I was off and running.

                   

                  I hope this will help out.

                   

                   

                  Austin

                  • 6. Re: How to make Tableau know a field is a date and not an attribute on a MS-AS Cube
                    Austin Dahl

                    I accidentally put up a workbook from our not-yet-released version.  Here's one that works with 5.0.

                     

                     

                    Austin

                    • 7. Re: How to make Tableau know a field is a date and not an attribute on a MS-AS Cube
                      Julius

                      Austin,

                      This is a great thread. I was curious how you made the local cube file. What tools do you use?

                      • 8. Re: How to make Tableau know a field is a date and not an attribute on a MS-AS Cube
                        guest contributor

                        Since we are based on restaurant based company, there are several times in a year that we are launching different products. We are getting the data from the franchisee on a daily basis to measure or analyze different trends of the product as how it is performing before launching and after the product is launching. So it means we are talking about pre-post analysis. Lets take an example, if a product is launched on 27 of Sep, 2009, we want to take one full month before this particular date and then the dates after 27 and at the upcoming months of October and November. Our calendar year is based on Dec to Nov. As far as the data sources are concerned, i am using MSAS 2000 SP4. How can we achieve this through Tableau. I am using the License Product. I hope someone can resolve this for us at the earliest as it becomes really difficult to do this kind of analysis.

                        • 9. Re: How to make Tableau know a field is a date and not an attribute on a MS-AS Cube
                          James Baker

                          Adil Majeed,

                           

                          That's a fairly unbounded request.  Might I recommend contacting your Tableau sales rep and asking about our Professional Services division?  There are some excellent folks whose mission is to come directly to customers and help them ramp up their analysis, infrastructure, and knowledge to "kick-start" the Tableau experience.

                          • 10. Re: How to make Tableau know a field is a date and not an attribute on a MS-AS Cube
                            guest contributor

                            Hello, I was just trying this and I get a "type mismatch" when I attempt this. Any suggestions?

                            • 11. Re: How to make Tableau know a field is a date and not an attribute on a MS-AS Cube
                              Kyle B

                              I apologize, I made the above post but didn't realize I wasn't logged in.

                               

                              I get the "Type Mismatch" - not sure what to do there.

                              • 12. Re: How to make Tableau know a field is a date and not an attribute on a MS-AS Cube
                                guest contributor

                                I saw the question about how to make local cube files, and did not see that it has been answered.  I hope I am not out of line here (I did not take time to read any "rules").  May I just suggest that you take a look at our product, CubeSlice, which works with MSAS to automate the production of local cube files. Best of luck to you!

                                 

                                 

                                www.cubeslice.com 

                                • 13. Re: How to make Tableau know a field is a date and not an attribute on a MS-AS Cube
                                  gall3on

                                  I just stumbled across this (after hours of searching) for the Cube Date to Measure issue.  Thanks for this, Austin! Made my life so much easier after I got it to work!

                                  • 14. Re: How to make Tableau know a field is a date and not an attribute on a MS-AS Cube
                                    Roxana Tudor

                                    Hello Austin,

                                     

                                    Could you please help me? I do not get where it's the issue  in my MDX expression. In my case, Tableau is connected to a SAP BW cube and the field Calendar Day Level 01 is recognized as string and not as a date. I have created a calculated mamber as you explained but I'm getting the error which you can see in the picture. I have tried to write the formula using CDate([Calendar].[Day].[Level].[01].CurrentMember.MemberValue) as well, but, unfortunetely I receive the same error.

                                     

                                    convert string into date.JPG

                                    Thank you!

                                    Regards,

                                    Roxana