|
|
The features on this page require a GENESIS64 Advanced license and are not available with GENESIS64 Basic SCADA . |
|
|
The features on this page require a GENESIS64 Advanced license and are not available with GENESIS64 Basic SCADA . |
Columns of type DateTime are automatically represented as hierarchical columns by AnalytiX-BI. The level names in a DateTime hierarchy are as follows:
· Year
· Month
· Day
· Hour
· Minute
· Second
Hierarchical columns allow access to the levels of the hierarchy as individual columns by specifying the level name after the name of hierarchical column name. The following query retrieves the year, month and day parts of the OrderDate column.
SELECT
Orders.[OrderDate.Year] AS Year,
Orders.[OrderDate.Month] AS Month,
Orders.[OrderDate.Day] AS Day
Note: When referencing a hierarchy level, the column must always be fully qualified with the table name; the column name is followed by a period and then the level name, and they both have to be enclosed in square brackets.
It is recommended that hierarchical access, although supported at all compatibility levels, is used for backwards-compatibility only. In the current version of AnalytiX-BI the preferred way of retrieving individual levels of a DateTime hierarchy is to use either the DATEPART function, or the individual functions to extract parts of a date and time (such as YEAR, MONTH, DAY, etc.)
The following query retrieves the same information as the previous query using the DATEPART function.
SELECT
DATEPART(OrderDate, 'year') AS Year,
DATEPART(OrderDate, 'month') AS Month,
DATEPART(OrderDate, 'day') AS Day
FROM Orders
Alternatively, the individual functions can be used to obtain the same result.
SELECT
year(OrderDate) AS Year,
month(OrderDate) AS Month,
day(OrderDate) AS Day
FROM Orders
For more information about the individual functions please refer to Date and Time functions.
In addition to DateTime columns, there are two other hierarchical columns available in AnalytiX-BI data models:
· AssetPath, produced by the Dimensions > Assets data flow step
· PointName, produced by the Dimensions > Historical Tags data flow step
The hierarchy levels in these columns are dynamic and depend on the structure of the data imported from AssetWorX or Hyper Historian.
As an example, consider the following data table that we have named Assets, connected to a data flow that ingests all assets under the default Company in AssetWorX using a Dimensions > Assets step:
Data Table Connected to a Data Flow Ingesting Assets
The data schema lists the AssetPath column (which is our hierarchical column) and the individual levels (Enterprise, Site, Area, etc.). Individual levels can be accessed by name in the same way as DateTime levels.
The following query selects the AssetPath column, along with the Enterprise, Site and Area levels.
SELECT
Assets.AssetPath,
Assets.[AssetPath.Enterprise] AS Enterprise,
Assets.[AssetPath.Site] AS Site,
Assets.[AssetPath.Area] AS Area
As in the case with the DateTime dimension, hierarchical access should only be used for backwards-compatibility. The current version of AnalytiX-BI, unlike previous versions, also exposes the level columns as individual columns in the table, and that is the preferred way of accessing those columns.
The following query retrieves the same data as the previous query by referencing the level columns directly without the need of hierarchical access.
SELECT
AssetPath,
Enterprise,
Site,
Area
FROM Assets