Can't understand variable logic for Measure vs. Dimension

[Former Member]

I have a report from two universes that I merged dimensions on, and my first effort worked great. However, I started to create some additional logic but nothing was working so I went back to the basics and now I'm very confused.

I have a report from the Audit Database, showing the measure "number of refreshes". In order to get a baseline of understanding for my additional logic, I tried something simple - create a dimension based off the measure. But I'm getting different results, and can't figure out why. What's going on ? I expected my variable to display the same value as the measure.

[amitrathi239 · Accepted solution]

Check what is behind the Number of actions in universe.I think this measure object is calculating the  sum of count of actions.Based on the number of dimension objects you have pulled in the webi query  on that you will get the count.

if you are creating dimension on this then use Sum function in variable and see if you will get the correct result or not.

=Sum([Number of Actions])

[Former Member · Accepted solution]

Amit,

very good, thank you.

I did like you said and it's working now. I figured I was doing something similar to "=A6" like in Excel, but it's all the cube stuff I suppose.

[Former Member]

Hey, just to close things out, I got it all worked out .

I was wrestling with the display of the totals, but with the help of my colleagues (and a Youtube video), I got the "folding" thing to work, and here it is . . .

the unfolded view looks ugly, and I was trying to jump through hoops to display the totals nicely. Then I came across the folding feature.

[amitrathi239]

what do you mean by folded/unfolded?

if you use the count(Login) in first column and remove the any user activity then BO will automatic sum-up the  columns and you will get the values in single row only.Yes you need to create some variables to calculate the  average.

[Former Member]

Amit,

the folding / unfolding is the collapsing of rows, just so the totals display. I think you have to put a break on first.

[amitrathi239]

I don't think so you needed this option  for this report.anyway your report is completed.

Try some time later what i have suggested.

[Former Member]

FYI - after I changed my variable to " Sum([Number of Actions])", per Amit, I was able to complete my entire task. It's a bit ugly, but I got the desired results.

My goal was to replicate this spreadsheet, to evaluate Report Usage

Then I figured out merging dimensions with the two queries, to join Audit (User Login) to our User d.b. (Login). I got the same stats but using brute force, just like a spreadsheet. I'm not expecting to haggle through how to do this, but if you can give it a quick glance and share tips on what might be a better approach, thx.

[amitrathi239]

This is the basic concept if you are using two queries then you need to merge the common dimensions at report level.

Please explain where you have faced the issues in variables or in data.

according to your excel you need to merge audit user/login user id along with both any user activity objects.

[Former Member]

Amit, I got it all worked out, data-wise .

So I was able to replicate my actual spreadsheet (of two separate queries) into WebIntelligence .

I'm having some issues / questions on formatting, but I'll post separately as needed. For now I completed my task, now just need to pretty it up . . .

Thanks again.

[CdnConnection]

Bill,

   The measure at the Universe much different than the Variable (Measure) at the Webi level.

At Universe:

• Look at SQL being created by Universe to see the GROUP BY statement being created
• i.e  = SUM (Table Column ) will Group the Data and reduce the ## of row being returned to WebI
• It also has TWO level of Aggregation settings.  1st Data Base level, defined by =SUM ( in front of field.  2nd is Project level, how the Measure should Aggregate at report level.

At WebI:

• NO Group By option. Looks at each record and does Aggregation
• Also makes a different what Objects are being displayed with measure.  Every record will show.

This is why you need test Measure at Universe &/or at report level to get best result.

Regards,

Ajay

[Former Member]

Ajay,

very good, thank you !

I see to ponder this a little more, but right at the moment I'm heading to another BO high priority task.

Bill

[Former Member]

Ajay,

very good and helpful, thanks again.