Advice on inserting data at parent level

[Former Member]

Hi experts,

I would need some advice on how can I enter data at parent level. As we cannot enter data at parent level I am figuring out different strategies to do so. The one I seem to like the most is to create members representing the parents where we can input the data. For example:

- 2008.Total (Parent)

+ 2008.Generic (Leaf)

+ 2008.Jan (Leaf)

+ 2008.Feb (Leaf)

+ etc....

I would need to do this in different dimensions in my application. So here are the questions:

1) Is there anything wrong about doing this?

2) Is there a better option? (what is the Best Practice in this case?)

3) In the case of doing this for the TIME dimension, there are some properties which value is not easy to set for those members (TIMEID, YEAR and LEVEL). I don't really know where and how those properties are used and the help doesn't help me much (what is the "time format required for Analysis Services"?). So what values have you used in these properties for such members?

Thank you very much,

Rafael

[Former Member · Accepted solution]

Rafael,

Did you think of, instead of creating a "dunmy" member in your time dimension, using the DataSrc for this?

lets say

TIME DATASRC

2008.jan input

2008.jan Adjustment

Hope this helps.

Nic

[Former Member · Accepted solution]

Hi Nicolas,

indeed that's an option I've considered, but I ruled it out as I don't really want to do that. If my TIME dimension goes in a hierarchy like YEAR-QUARTER-MONTH, what I want is to simply input values at both month and year levels.

I could have a DataSrc dimension to differentiate a real month value from a year value (both at 2008.JAN for example), but I think that is quite awkward and less clear than the other option I proposed. The counterpart with it is that I don't know how to set up appropriately the properties of the TIME dimension.

Cheers,

Rafa

[Former Member · Accepted solution]

Rafael,

It would be me, I would not play with the time dimension, for the simple and good reason that it is a Microsoft thing (thought SQL Server) and not a SAP BPC thing!! Playing with it could be messy...

I also understand why you dont really like what i told you.

But to be honest, I do not see easy way to do this unfortunately...

Nic

[Former Member]

Hi Rafael,

What purpose is your application designed for? In a legal consol application, would never mess with the standard time dimension setup, in particular the time dimension. The setup of the Time.Level and Time.Year properties are very sensitive in the operation of some of the business rules, particularly the opening balances & account transformation rules (where there's a time-period offset functionality). I always try to retain the standard time dimension setup there.

This means, for example, using a seperate datasrc member (or members) if the customer insists on having "13th period" for year-end adjustments. If they don't go into December along with the standard December values, then the carry-forward opening balances for the following year can be very tricky to calculate.

If your application is purely analytics, or for planning, then you should still understand what business rules features you want to use before deciding the time dimension design. When I've had year-total input data, I either

1.) use a standard 12-month time dimension, and by practice always put total-year data in December. Set up input schedules appropriately, and train people appropriately. Use Dec not Jan, or else the year-total and periodic/YTD computations in the cube won't work correctly. And consider your Account.Acctype carefully.

2.) If the data is for annual values that aren't directly part of the P&L or B/S, and are things like allocation drivers, tax rates, inflation rates, stuff like that -- which doesn't get added up along with the monthly values to come to a total-year "full picture" -- then I follow the structure from the old Outlooksoft demo. Create each of your years with the standard 12 months. Then create

Input.Year

xxxx Input.Quarter

xxxxxxxx 2008.Input

xxxxxxxx 2009.Input

xxxxxxxx 2010.Input

etc.

Then set your YEAR = 2008,LEVEL=MONTH for 2008.Input, etc.

This approach works well in logic *LOOKUPs and also keeps the dimension structure clean & intuitive for end users. If you put your 2008.Input under 2008.Q1 or 2008.Q4, then every time someone lays out or drills down in a report, they will curse the designer, since they now need to remove that extra column of the 2008.Input member.

With the datasrc approach for the "13th month" you can control whether the year-end adjustments are there or not, for those few reports where you'll want to see them. And 99% of the time, your time dimension "looks" normal.

I'm sure there are other approaches beyond what I've discussed, but I've never gotten into trouble using one or both of these two.

Regards,

Tim

[Former Member]

Hi,

The application I'm working on is for a general purpose calculation. I mean that it is not a consolidation app nor a purely financial one, just some input data (mainly about assets) and calculations from them (nor a planning app either). At this point I don't intend to use Business Rules (but it is a good point to consider, I didn't think of it before nor the acc type matter).

The problem is that I have data at different levels in some dimensions and I would like to avoid creating a cube for each of the situations. Mainly, I have a TIME dimension and an ASSET dimension following a hierarchy, and I have input data and calculated values at different levels in both dimensions. That's why I thought of using members representing the parents, and so far I think it can be a good approach, at least for all dimensions but the TIME dimension. From your answers I can see that we should do anything to avoid manipulating it. Now, to see if I follow your answer Tim, you suggest:

#1) Use standard 12 month time dim and use december to store annual values, creating a custom dimension to keep appart proper dec values from annual values.

#2) Use a different group of members to keep input data at year level.

In my case, I am going to calculate values at year level that should be aggregated along with the monthly values in the total year level, so I think that I cannot use the solution #2, so I consider #1 as the best option.

Now, I add the option I considered in the first place (only to give it a final chance)

#3) Add a member to the TIME dimension named "13th month" or "Year" value or whatever.

2008.Total

::::::2008.Q1

::::::::::::2008.JAN

::::::::::::2008.FEB

::::::::::::2008.MAR

::::::2008.Q2

::::::::::::2008.APR

::::::::::::2008.MAY

::::::::::::2008.JUN

::::::2008.Q3

::::::::::::2008.JUL

::::::::::::2008.AUG

::::::::::::2008.SEP

::::::2008.Q4

::::::::::::2008.OCT

::::::::::::2008.NOV

::::::::::::2008.DEC

::::::::::::2008.GENERIC

It could be at the month level or the quarter level. TIME dimension is so tricky that we should go for #1 and create another dimension rather than just creating another member in it (#3)?

Regards,

Rafael

[Former Member]

Hopefully someone who's used your option #3 before, can speak to its pros & cons.

Personally, I'd take option 1, and I would only use that other dimension if it's really necessary. If users want to key in the yearly total into December, let them. If they want to spread it out among the twelve months, let them.

The problem with getting another dimension involved in it, is that your input schedule then would only show one or the other base member of that second dimension. (If you don't have input schedules to worry about, then it's less of an issue.)

Your #3 reminds me now -- I believe it's a requirement that your "month" suffix in the time dimension not exceed 3 characters. So my earlier example of "2008.Input" should be 2008.INP, and your generic should be 2008.GEN or .FIN or .END. I got into a bit of trouble once (a few releases back -- and it may have been on SQL 2000, not 2005) when my customer want to rename months into something longer than 3 characters. Some of the logic features, and/or the time dim processing stopped working. I forget exactly what failed, but ever since I've just stuck with JAN, DEC, W02, W53, and so on.

[Former Member]

Thank you about that tip Tim.

In my case I need both, values at month level and at year level (month level is not just a periodification of year), so I would need to use the "user Timely" dimension.

As I would like to avoid introducing another dimension, please, anyone can confirm to having used option #3 previously mentioned in this thread without troubles? (that is, creating a 13th member in time dimension).

Cheers,

Rafa

[Former Member]

Hi all,

as I can see, no doubt that modifying the standard Time dimension is quite unusual. I think an operative calendar with 13 or 14 periods could be useful in some cases....

To put it in a different way, let's say we have a custom Time dimension and want to test if it works. What should we test? It processes without any errors, but as I have been able to see that is not much of a guarantee that it is going to work ok. So what should we need to test? (Ev formulas? mdx formulas? script logics? BPFs? All of them?). In other words, if anyone tried to make a custom Time dimension and it didn't work....what is it that didn't work?

Kind Regards,

Rafa

[Former Member]

All right, so I see the point: "do not mess with time dimension"

Thank you all for your answers.

Rafael

[Former Member]

No no no! Not at all... Definitely mess with it, that's how I learned what I know.

I'm sure there's a lot more I don't know, and we need you to mess with it, too.

Just don't mess with it in production, or if you're on a tight deadline. And expect for something unexpected to go ker-blooey. 😃

[Former Member]

All right, got it

I'll get my priorities done and then give it a try. I'll let you know about it

Cheers,

Rafa

[Former Member]

Hi Rafael,

It seems like the way you have described is the best. We have asked consultants from SAP about this issue and they have proposed this way.

I am working with 7.0NW and do not know how Analysis Services works.

In our system we don't have any hierarchy on time dimension and calculate totals with script logics... we are going to integrate the system with bi and the way of calculating year total with script logics seems to be better for us.

[Former Member]

Hi Rafael,

Thank you for your quick answer. I would like to ask you further about what you said in your reply (concerning formulating parents), but as I still need an answer about the TIME dimension I will open a new thread for that, ok?

Regards,

Rafa