Is it too hard compare two years?

[Rosanero4Ever]

Hi all,

using a relational model, when we need to compare two years about a revenue, we must create two different items and for each item we must use an IF, THEN, ELSE statement in order to calculate a revenue for a particular year.

When I explain this practice to my customers (expecially customers with a low technical background), they look at me as a crazy consultant because in this way  is difficult perform a simple compare.

Does exist other practices? How do you explain the practice I described above to your customers? And what's their reaction?
Thanks a lot

[BigChris]

If it's something that you do a lot then it might be worth building that into the model in Framework Manager. That way the user will just see the two pre-built with and won't have to worry about the calculations that get you there.

[Rosanero4Ever]

This solution is ok if you want to compare current year and previous year. How can i model this in FM if i want to compare years based on two generic filters "first year" and "second year"?

Inviato dal mio LG-D802 utilizzando Tapatalk

[BigChris]

Depends on what you want to create and how futureproof you want to make it. You could create fields for individual years in the past as well as x years into the future, so that you'll have a field for each of 2014, 2015, 2016 etc. The downside of that is that the model will need to be updated when you get to the end of the years into the future that you've specified.

An alternative would be to create fields for CurrentYear, CurrentYear-1, CurrentYear-2 etc. That would be flxible and wouldn't need to be update...but you wouldn't have nice headings for the years.

[Lynn]

This is where the OLAP or DMR approach warrants consideration. It is very easy to do that sort of period vs period comparison using a cube-style data source rather than a relational one.

If you try to build it into a relational one you'll make yourself crazy because if you do it for year then they'll say how about quarter over quarter or this month compared to same month last year or latest 3 weeks compared to prior 3 weeks...etc. etc. etc. to infinity and beyond.

[BigChris]

Completely agree Lynn, I was just trying to come up with something that would work in the relational model.

[Rosanero4Ever]

ok Lynn, I agree with you. But, some customers (expecially that with a low technical background), hate dimensional model because is more difficult use dimensional functions instead of relational functions. For example, a filter between two dates is simple in relational model but it is more difficult in a DMR model.

So, customers could use relatinonal model, i.e. simple functions but difficult comparison between year(for example), or customers could use OLAP model with difficult functions.
It's hard in both the ways...

[MFGF]

ok Lynn, I agree with you. But, some customers (expecially that with a low technical background), hate dimensional model because is more difficult use dimensional functions instead of relational functions. For example, a filter between two dates is simple in relational model but it is more difficult in a DMR model.

So, customers could use relatinonal model, i.e. simple functions but difficult comparison between year(for example), or customers could use OLAP model with difficult functions.
It's hard in both the ways...

I'm not sure I'd agree with you on a dimensional model being more difficult to use. Generally dates in a dimensional model are arranged in a hierarchy, and it's easy to drag in quarter or month members to get the dates you require. Often filters are not needed at all since the member tree is available. You also have the concept of context - you can drag year/quarter/month/date members into the context area at the top and change the context of the measures in your report - again without having to filter or use functions.

MF.

[Rosanero4Ever]

MFGF, ok for the context area but what do you think about a report with a prompt page having a date prompt useful to filter from a date to another date? Are you agree is more difficult?

[Lynn]

ok Lynn, I agree with you. But, some customers (expecially that with a low technical background), hate dimensional model because is more difficult use dimensional functions instead of relational functions. For example, a filter between two dates is simple in relational model but it is more difficult in a DMR model.

So, customers could use relatinonal model, i.e. simple functions but difficult comparison between year(for example), or customers could use OLAP model with difficult functions.
It's hard in both the ways...

A good dimensional model can easily satisfy a lot of basic reporting requirements just as the muppet says, but you are correct that there can be a learning curve. This is especially true as you move into more complicated requirements. A well designed model goes a long way toward easing that burden, just as it does in the relational world.

MFGF, ok for the context area but what do you think about a report with a prompt page having a date prompt useful to filter from a date to another date? Are you agree is more difficult?

The design approach you are suggesting is just not suited to the dimensional world. With a relative time hierarchy it is dead-bang-slap-the-side-of-your-head simple to do time prompted for a dimensional report. The effort comes in defining the relative periods of interest in the organization and building an appropriate model to support the need.

We can debate the degree of learning, but the bottom line is that we need to use the right tool for the right job. The same job can often be accomplished using different tools. It is the frequency of our need to do a particular type of job along with our available time and resources that suggest our optimal tool choice. We need to consider those factors along with how much time and money we are willing/able to invest in acquiring and learning new tools.

I can paint a fence with just a paint brush and some elbow grease and get the job done well enough. If I need to paint dozens of fences on a regular basis, and need to do it with some degree of time pressure, then I would be well advised to buy a sprayer and figure out how to use it without also changing the color of nearby the shrubs, lawn, and household pets wandering past. If I stay in my comfort zone of paint brush and elbow grease then I'd go broke trying to make a living as a fence painter.

I believe that analytically advanced organizations can outperform their competitors. If they want to stick with what they know then they have a higher likelihood of getting left behind. Things that are unfamiliar may seem more difficult, but once you learn they are not as daunting as they may have appeared at first.