[nQSError: 22040] To use AGO function, the storage level of the query ('[time1.time_key]') must be a static level. (HY000)

This error may occurs most probably due to improper Time dimension hierarchy.

To use AGO or TODATE time series functions, it is recommended that, we should define the detailed level also in the Time dimension hierarchy.

Eg : Suppose you have a time dimension table, that includes columns Time_key, Date_value, Month, Quarter, and Year. And If you have created the Time dimension hierarchy with the levels such as YEAR, QUARTER and Month only (there is no level defined for the column Date_value, which describes the granularity (level of detail)of the row.).

• This works fine, for drill down, level based measures and to define aggregate tables. 
• But when you try to use AGO or TODATE function with this Time dimension hierarchy, you will get the above mentioned error.

Solution : Just include one more logical level for the Time dimension hierarchy and add the date_value(or a column which holds granularity for the row.) as a logical key.

[38087] None of the levels in time Dimension '' defines a chronological key.

For a Time Dimension, it is mandatory that at least one logical level must have set Chronological key. If a chronological key is not set for a Time dimension, then we will get the following error when you check the consistency.

Solution : Expand the Time dimension, go to properties of any logical level, then go to keys tab and check the Chronological Key tab.

Level Based Measure in OBIEE

A level-based measure is a column whose values are always calculated to a specific level of  aggregation. For example, a company might want to measure its revenue based on the country, based on the region, and based on the city. You can set up columns to measure CountryRevenue, RegionRevenue, and CityRevenue.

To set up these calculations, you need to build a dimensional hierarchy in your repository that contains the levels Grandtotal, Country, Region, and City. This hierarchy will contain the metadata that defines a one-to-many relationship between Country and Region and a one-to-many relationship between Region and City. For each country, there are many regions but each region is in only one country. Similarly, for each region, there are many cities but each city is in only one region.

Next, you need to create the logical column CountryRevenue in fact table  in BMM layer.
Right click on the fact table -> New Object -> Logical Column.

Then, Give a name to the measure, then under Aggregation tab, select the appropriate aggregation rule(SUM is the Aggregation for the measure CountryRevenue, in my case), then under the Level tab, select the appropriate logical level (country is the logical level for the measure CountryRevenue, in my case). Then say OK.

Repeat the same process for the other tow measures (RegionRevenue, and CityRevenue).

Now map the these logical measures to the physical source (Revenue is the physical source for these measures in my case.) To map the column CountryRevenue to the physical source column Revenue, follow the below steps :

Double click on the measure, called CountryRevenue, goto Data Type.You will find the logical Table source, double click on it.

Uncheck the option Show Mapped Columns, then all the unmapped columns will be displayed. In our case CountryRevenue,RegionRevenue, and CityRevenue are the unmapped columns. So, you should be able see all these columns there.

Now click on the dotted button (highligted in above picture) beside the measure CountryRevenue to map to physical source column. Then select the appropriate phisical source column from the Expression Builder and say OK.

Repeat the same procedure for other two columns.

Final step is, make sure that all the three measures should be listed under the corresponding logical Level in the dimension. In my case, the CoutryRevenue must listed under Country logical level, RegionRevenue must listed under Region logical level, CityRevenue must listed under City logical level in the dimension Product.

These measures are specific to the defined logical level. So CountryRevenue measure will always gives us the Revenue to the Country level, even if you add the Region dimension column in between Country and CountryRevenue  columns. RegionRevenue measure will always gives us the Revenue to the Country level, even if you add the City dimension column in between Region and RegionRevenue  columns.CityRevenue measure will always gives us the Revenue to the City level.

Thank you,