Use these guidelines when modeling time series data:

• Use a time series function when the data source contains history. A data source that contains history might use a star or snowflake schema with an explicit time dimension table. A normalized, historical database might include a time hierarchy with levels in a schema similar to a snowflake. A simple date field isn't adequate for use with a time series function.

• Oracle Analytics Server requires the time dimension physical table or set of normalized tables that are separate from its related physical fact table.

  A common source schema pattern is a fully denormalized table that has time dimension columns are in the same table as facts and other dimensions. This common source schema pattern can't qualify as a time dimension, because the time dimension table is combined with the fact table. Because you can't change the source model, you can create a SELECT statement of the physical table containing the time columns to act as the distinct physical time dimension table. You must join the SELECT statement time dimension to the physical table that contains the facts.

• In the physical layer, the time dimension table or lowest-level table in the normalized/snowflake must join directly to the fact table without any intervening tables.

• The tables in the physical model containing the time dimension can't join to other data sources, except at the most detailed level.

• A member value must be physically present for every period at every hierarchy level. They must not contain rows that are skipped in the sequence. You don't need a fact data for every period. Only the dimension data must be complete.

• You must model each unit of distance between members such as month, half, or year, in a separate hierarchy level.

See Create the Logical Time Dimension in a Semantic Model.