Exception Views

[jim wood]

Guys,

Is there a quick and easy of creating views that are exception based? For example were you have demand but no capacity? Filter views won't do this. I was looking at the filter on the cube export but my mind has left me.... (It is a monday after all!!)

Jim.

[Alan Kirk]

Is there a quick and easy of creating views that are exception based? For example were you have demand but no capacity? Filter views won't do this. I was looking at the filter on the cube export but my mind has left me.... (It is a monday after all!!)

Not really a quick and easy one, no. Though if the elements that you're checking for are well defined...

We do have one chore which interfaces data from one cube to another. At the end a process creates a view to return all of element A's values for the period, which should have corresponding values in element B. T.I. iterates through that view checking element B's value for the combination, and wherever element B is 0 or negative, spits that out via AsciiOutput. (Actually there are some other checks done as well, but that's the gist of it.) The Ascii file, if any, is loaded into Excel via the same VBA code that triggers the chore. Et voila, an end report of exceptions loaded up in Excel (and formatted via VBA as well).

However this only works because we know exactly what we're checking for, and it doesn't change that often. It's not a method that would work that well if more "fuzzy" exception checking was needed.

[jstrygner]

Is there a quick and easy of creating views that are exception based? For example were you have demand but no capacity?

Isn't subset with MDX such a method?

Once you prepare and MDX that checks values in two or more columns, it is easy to change columns and conditions when you need to change your exception investigations.

[Alan Kirk]

Is there a quick and easy of creating views that are exception based? For example were you have demand but no capacity?

Isn't subset with MDX such a method?

Once you prepare and MDX that checks values in two or more columns, it is easy to change columns and conditions when you need to change your exception investigations.

The thought of using MDX did occur to me, but since we have only a kinda-sorta implementation of MDX in TM1 it's not immediately obvious to me how Jim's goal could be achieved using it. But if you have examples that you'd like to share I'm sure I wouldn't be the only one interested...

[lotsaram]

Using active forms I would say it is now incredibly easy to create exception reports with logic for example:
where measure A > x and measure B < y
To produce a filtered list of elements and be able to swap measures, values, time periods etc for the filter. This is possible even without advanced knowledge of MDX.

[jstrygner]

But if you have examples that you'd like to share I'm sure I wouldn't be the only one interested...

Alan, either there is something I am missing here, or I don't know, why you asked it

Nothing more than you can find in MDX_Primier. You build what you want (even compare values between cubes), especially if you do not have the 256 characters limit (I assume jim is on 9.5.2).

Using active forms I would say it is now incredibly easy to create exception reports with logic for example:
where measure A > x and measure B < y

And yes, good tip from lotsaram, which led me to try... ISB - it also enables adding own column formulas somewhere on the right and apply Excel filter, what is more, you can slice and dice without coming back to CubeViewer.

[jim wood]

Using active forms I would say it is now incredibly easy to create exception reports with logic for example:
where measure A > x and measure B < y
To produce a filtered list of elements and be able to swap measures, values, time periods etc for the filter. This is possible even without advanced knowledge of MDX.

I've not really used active forms, but if I remember rightly aren't these excel based? I'm trying to do this in a view. I'm not using excel. Apologies if I'm mistaken.

[jim wood]

Isn't subset with MDX such a method?

Once you prepare and MDX that checks values in two or more columns, it is easy to change columns and conditions when you need to change your exception investigations.

MDX now there are 3 letters that make me twitch. I'll have a look but I'll probably end up throwing my laptop out of the window!!!

On second thoughts I might try Alan's TI option. I've always been a bit of a coward.

Thanks for all your help though guys,

Jim.

[lotsaram]

I've not really used active forms, but if I remember rightly aren't these excel based? I'm trying to do this in a view. I'm not using excel. Apologies if I'm mistaken.

Hi Jim - why does the exception report have to be in a view and not Excel? You could do it with a view if you had a helper string cube to capture parameters and made extensive use of StrToMember function and .CurrentMember but the MDX would become very complicated. Versus doing it in an active form is I would say dead easy and allows for fancy conditional formatting with all Excel goodies and also can be used in TM1 web.

Paramatizing MDX by parsing values in Excel is not too complicated (and at least 1 or 2 orders of magnitude less complicated than trying to do it with pure MDX outside of Excel). You don't need anything more than a active form using the MDX parameter in the Tm1RptRow function. So what exactly is the aversion to active forms and MDX?

I have some worked examples but I would need to get permission from a former employer to be able to drop them in the tips & tricks section... If you have a cube you can share (for example hypothetical demand>0 capacity<=0) then I could bash out an example, wouldn't be too much trouble.

[jim wood]

Cheers Lotsa. If you could pass an example I would be grateful. If your not able to don't worry. I spoken to the powers that be and I think I have managed to convince them that doing an active form in excel might be the best way forward.

The original plan was to try and do it in a view as we are still not sure how this is going to be deployed over a WAN,

Jim.

[PlanningDev]

Just a thought but do you have Cognos BI? This would be extremely easy to do using query or report studio.

[jim wood]

No CognosBI available but thanks,

Jim.

[Alan Kirk]

But if you have examples that you'd like to share I'm sure I wouldn't be the only one interested...

Alan, either there is something I am missing here, or I don't know, why you asked it

Nothing more than you can find in MDX_Primier.

You mean this part of the MDX Primer, considering that the original question was how to build a view which you then indicated could be done with MDX?

TM1 currently (as of 9.0 SP2) only allows users to use MDX to create dimension subsets and not to define cube views.

You're right, I can't imagine what I was thinking of when I asked you to demonstrate how you planned to do that.

[jstrygner]

You mean this part of the MDX Primer, considering that the original question was how to build a view which you then indicated could be done with MDX?

No, I didn't intent to suggest you can construct a view via MDX. I just meant you can influence elements presented in particular subsets that already are part of a view. I thought this was enough to meet jim's needs.
Now I know why you asked your question and what "I was missing there" while wondering why you did it.

You're right, I can't imagine what I was thinking of when I asked you to demonstrate how you planned to do that.

I have a feeling you got insulted by my comment.
If so, my apologies. Believe me, it was not my intention to insult you.

[Alan Kirk]

You mean this part of the MDX Primer, considering that the original question was how to build a view which you then indicated could be done with MDX?

No, I didn't intent to suggest you can construct a view via MDX. I just meant you can influence elements presented in particular subsets that already are part of a view. I thought this was enough to meet jim's needs.
Now I know why you asked your question and what "I was missing there" while wondering why you did it.

You're right, I can't imagine what I was thinking of when I asked you to demonstrate how you planned to do that.

I have a feeling you got insulted by my comment.
If so, my apologies. Believe me, it was not my intention to insult you.

OK, I see what you meant now, you see what I meant now.

Just a misunderstanding between friends, don't sweat it.