Datacube Comparison Quesion

I apologize in advance for what I’m sure is a newbie question. I’m quite familiar with BI practices, but am only a few weeks deep into the Dundas methodology.

We’re trying to compare the output of 2 cubes (specifically some sums) and output a percentage. I’ll skip over all the business-specific jargon and go back to everyone’s favourite sample data: produce.

Table “Produce” has 3 columns: ID, Name, Category.

Sample Data:
ID Name Category
1 Banana Fruit
2 Apple Fruit
3 Cucumber Vegetable
4 Celery Vegetable
5 Orange Fruit

The Big Boss now would like to know what percentage of all produce are Fruits.

So far, I’ve created 2 cubes: one outputs all produce and there other further filters the output of the first to return only fruit. I can easily get the totals for both in their own metric sets. The wall I’m hitting is that I can’t figure out how to do a mathematical comparison between the two. I can’t add the first cube to the second as that generates our beloved “same source” error, same with adding both cubes to the same metric set. Thus, I have nowhere where I can type “return count($fruit$)/count($all$)” or whatever the syntax would be.

Obviously I can solve this by doing the math directly in SQL, but I’ve been working really hard to avoid SQL whenever possible as it means that few people other than myself will be able to maintain the reports.

Any and all ideas are very much appreciated in advance!

1 Like

Hello,

If you add the data from both data cubes onto a dashboard, I believe you can use a formula visualization to do whatever calculations you’d like between them:

https://www.dundas.com/support/learning/documentation/design-view/formulas/using-a-formula-visualization

This may help to solve what you are trying to accomplish.

1 Like

That’s exactly what I was looking for! I didn’t realize that formulas could look at inputs from outside of their own metric set/data cube “container”. Just a paradigm adjustment from what I’m used to do Dundas, I suppose. Thank you very much for your help, Terrence!

1 Like

No problem, glad you found a solution!