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?
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].
1. In the Schema viewer, click on the triangle and select Calculated Members...
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
4. Enter the formula for the date measure.
For Analysis Services 2005, for the [Date].[Calendar] hierarchy the formula will be
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.
6. Get fancy with trend lines and colors or date filters.
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.
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, 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
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.
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.
This is a great thread. I was curious how you made the local cube file. What tools do you use?
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.
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.
Hello, I was just trying this and I get a "type mismatch" when I attempt this. Any suggestions?
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.
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!
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!
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]..CurrentMember.MemberValue) as well, but, unfortunetely I receive the same error.