Building an hierarchy in Universe

[Former Member]

Hi,

Please help us how to build a Hierarchy based on particular values. i.e we need to create an hierarchy in universe level(for ex: Derived Table) and database is SQL Server.

hierarchy : Year->Quarter->month-> date

For date object the query is current date value. (this we can get)

Month -> It should be last day on of the day value in that particular month(i.e if jan is the month then we should get 31st value as month value)

Quarter -> It should be last day value of the quarter (i.e if quarter =2 then we should get quarter 2 value as june 30th value)

Year-> last day of the year as year value.

Thanks In Advance

[former_member793810 · Accepted solution]

Hi Suresh,

So you want to create a Hierarchy where you would be combining multiple dimensions such as Year > Quarter > Month > Date.

Create a new hierarchy from hierarchy editor, Tools > Hierarchies.

When hierarchy editor opens you will see all available objec in your universe on the left side of the pane.

You have to add required objects on the right side.

When you are creating new hierarchy you are creating new new folders in the custom hierarchy pane and then adding required dimensions.

Here are the steps...

1. From the Hierarchies editor, click the New button.

2. Type a name for the hierarchy.

3. Press RETURN to apply the name.

4. Select the new hierarchy.

The hierarchy is highlighted.

5. Expand a default hierarchy node in the left pane.

This is the hierarchy that contains dimensions that you want to add to the

new custom hierarchy.

6. Click a dimensions you want to add

Now come to your other issue for selecting last day of the month etc. For this you will have to write down formulas to achieve this functionality.

Regards,

Bashir Awan

[Former Member · Accepted solution]

Hi Bashir,

As per my requirement I have to create the hierarchy at universe level itself as I need to create few dashboards based on this Time hierarchy.

Thanks & Regards,

Suresh Babu Aluri.

[former_member793810 · Accepted solution]

Suresh,

Can't you create hierarchy atuniverse level using above mentioned steps ?

Regards,

Bashir Awan

[Former Member · Accepted solution]

Bashir,

Please go through my requirement clearly. I know how to create a hierarchy in universe level. Before creating hierarchy we need to create those objects in universe level. I got struck there.

i.e hierarchy : Year->Quarter->month-> date

Here I need how to write the queries for the below objects (my back end is sql server)

For date object the query is current date value. (this we can get)

Month -> It should be last day of the month in that particular month but if last date is null then it should be the day before last date and so on...(i.e if jan is the month then we should get 31st value as month value)

Quarter -> It should be last day value of the quarter but if last date is null then it should be the day before last date and so on (i.e if quarter =2 then we should get quarter 2 value as june 30th value)

Year-> last day of the year as year value but if last date is null then it should be the day before last date and so on.

Cheers,

Suresh Babu Aluri.

[former_member793810 · Accepted solution]

Ok

[Former Member · Accepted solution]

Hi Suresh,

Do you able to find the solution for this. Because i am lalso looking for the same thing for my user requirement.

Please help me

[Former Member · Accepted solution]

can we see the sql codes for all hierarchy we create? i need this to solve parent child relationship ????

[Former Member · Accepted solution]

Hi,

We can see the backend code OF sql in Webi Rich Client.

As far as coding date, quarter, time concerned, writing a code will be tedious so get the TIME HIERARCHY from SAP and implement. With this hierarchy you can create one more new hierarchy easily and then hide the previou8s hierarchy.

[Former Member · Accepted solution]

This thread over at the BOB forum should help you with the code for the relative dates.

http://www.forumtopics.com/busobj/viewtopic.php?t=152613

Please note that the objects will not parse when you build them because they do not reference a table.

However, they will work whenever you use them with an object that references a table.

These relative date objects are useful for building predefined conditions for scheduled reports - Sales Date between Start of Previous Month and End of Previous Month, for example, could be built once you have built the two corresponding relative date objects.

In terms of hierarchy, you would typically build a hierarchy against "physical" dates - those associated with facts - order date, order week, order month, order quarter, order year and so on.

I think you need to be clear on what you are trying to do because you seem to be confusing the two concepts.