Can any one help me on this?
I hope Tableau allow us to create all kind of complex calculations against CUBE.
Will you please help me on these Calculations
May I know please, Does Tableau allow us to create all kind of Calculations against Cube or is the any limiatations ?
Thanks in Advance.
Thanks & Regards
So you are correct in that to create dimensions in Cubes (such as a YtD Filter) you need to use Calculated Members, which are coded using MDX. Unfortunately I'm no expert in MDX. I did (a while back) do some Tableau training for a group who did use cubes so did a bit of R&D on the subject...
In the below post, I've attached a workbook where I created a few Calculated Members using MDX... however I hacked these few 'Calculated Members' together from stuff I found on the Web (sort of reversed engineered others works, with not a huge amount of understanding on my part!!)
I also found this article at the time, which I found very useful
...although it ends like most articles on Tableau & Cubes in that it recommends
try and talk your company into creating a relational database, to connect to Tableau
Hope this helps
I'm totally the wrong person to ask for help here. My specialty is Tableau Server administration.
Dell - Internal Use - Confidential
You cannot use a Dimension in calculated field or in parameter when you have CUBE as a data source.
Apologies on this one, I don't have any experience working with SSAS Cubes, but I know they follow much different rules in Tableau.
Thanks Simon for replying to my Query,
Sorry Simon Runc, I am working on Proof of Concept , It is a big challege for us to Demonstrate to our clients about Tableau Functionality, it turns into Client choose the Product for Analysis.
Client Team has shared a plain SSAS Cube, they are not ready to create filters at Cube level, since they want to test Cube functionality at Tableau level.
I suggested them Once we connect to the Cube then Copy the required data then paste in Excel then Connect Excel to Tableau to provide the soltions, but they are not accepting it. since it is rework updating the Excel.
I hope some other EXPERTS ( Kettan, Jonarthan Drummy, Tom W, Mark Fraser, KK Molugu, Alexander Mou,.............) also will involve to solve it.
Sorry I couldn't be more help! and understand the Client restrictions (often they've spent a lot of time/money building the business rules into their cubes...and are hesitant to loose them!). As you'll see from my few examples you can create these as 'Calculated Members' but need to know MDX.
If you look, for example at the [2008 Sales Rank by State] calculated member
[Order Date].[Year Name].
You can see how you can pick up a Year (in this case 2008). So you'd need to write something similar to pick up the last week of the last year, and then restrict a calculated member to only includes these weeks. Alternatively these cubes can be 'pre-created' in SSAS (by the Client)
...It might be worth looking on Stack Overflow (or similar) where you'd find MDX experts. I could be wrong, but there are probably very few of these in the Tableau Community, and what you need here are MDX experts more than Tableau experts.
2 of 2 people found this helpful
Fortunately, I regularly have to work with a ssas cube and also have to create calculations for users with no mdx that they can later modify for their use - needless to say, working in a mdx-less method is difficult but can be done.
First, I have to ask, whilst it is clear that these filters / calculated members do not already exist in the cube, are there any long-term plans to develop them? Not having them could be problematic going forward even for users connecting through Excel.
In order to get around this assuming you only have one continuous date would mean you will need to create window calcs in order to hinge your calculation from so for example (omitting aggregate functions here as cubes are precalculated):
Drag your Date dimension onto rows, filter if need be, you probably don't want everything; then drag you value (we'll call it sales) to the text.
Next here comes the window: Window_Sum(Sales,Last(),Last()-364)
As dates are usually assembled descending from the cube, the latest date will be valued 0 from the Last() function and Last()+364 will take you back 365 days or one year from now.
This new calculation is now the total sum for the ytd. Using the indexing functions you are able to manipulate the data to your needs mostly without mdx.
Hope this helps
Edited: Window_calc was the wrong way around
Thanks for Reply.
Apologies for my late response,
I have to provide
CY MTD vs PY MYD, CY YTD vs PY YTD ......
Will try ouy our approach.
Hi Karthik Kumar,
I also have to create calculated members like YTD and Last Year Same Period, and have Date hierarchy available with only Month Name.
I am able to create the above values by hard coding the Month Name but would like the same to be calculated using the today/now function so that every month I don't need to manually update the same.
Please reply with whatever information you can share.