Example codeFactsOnQuery: Boolean;
FactsOnQuery: boolean;
The FactsOnQuery property determines whether the facts dictionary is based on a query.
Available values:
True. Facts dictionary is based on a query. The query text is determined by the IMetaAttributes.SelectQueryText property returned by the IRubricator.Facts.Attributes property.
False. Facts dictionary is not based on a query.
Executing the example requires that the repository contains the following:
Data table with the T_TSDB identifier that contains the following fields:
| Identifier | Field type | Comment |
| COUNTRY | Integer | Mandatory and included into the primary index. |
| INDICATOR | Integer | Mandatory and included into the primary index. |
| DL | Integer | Mandatory and included into the primary index. |
| VALUE | Real | Optional. |
| DT | Date | Optional. |
MDM repository with MDM_REPO identifier that contains the following:
The countries dictionary with the DICT_CTR identifier.
Factors dictionary with the DICT_IND identifier.
Add links to the Mb, Cubes, Rds, Dal, Dimensions system assemblies.
<font color="#008080">Sub</font> UserProc;<br /> <font color="#008080">Var</font><br /> mb: IMetabase;<br /> ci: IMetabaseObjectCreateInfo;<br /> o: IMetabaseObject;<br /> r: IRubricator;<br /> rds: IMetabaseObjectDescriptor;<br /> CountryFactAttr, IndicatorFactAttr: IMetaAttribute;<br /> FactIndex: IMetaIndex;<br /> fDict, rDict, vDict: IMetaDictionary;<br /> s: String;<br /> Atts: IMetaAttributes;<br /> <font color="#008080">Begin</font><br /> mb := MetabaseClass.Active;<br /> <font color="#008000">// Create time series DB<br /> </font> ci := mb.CreateCreateInfo;<br /> ci.ClassId := MetabaseObjectClass.KE_CLASS_RUBRICATOR;<br /> ci.Parent := mb.Root;<br /> ci.Name := <font color="#800000">"Time series DB on query"</font>;<br /> ci.Id := Mb.GenerateId(<font color="#800000">"RUB_QUERY"</font>);<br /> o := mb.CreateObject(ci).Edit <font color="#008080">As</font> IMetabaseObject;<br /> r := o <font color="#008080">As</font> IRubricator;<br /> <font color="#008000">// Setting RDS repository<br /> </font> rds := mb.ItemById(<font color="#800000">"RDS_REPO"</font>);<br /> r.Database := rds.Bind <font color="#008080">As</font> IRdsDatabase;<br /> <font color="#008000">// Specify that factors dictionaries<br /> </font> <font color="#008000">// of values and revisions are based on query<br /> </font> r.FactsOnQuery := <font color="#008080">True</font>;<br /> r.RevisionsOnQuery := <font color="#008080">True</font>;<br /> r.ValuesOnQuery := <font color="#008080">True</font>;<br /> <font color="#008000">// Time series have a compound key<br /> </font> r.CompoundFactorKey := <font color="#008080">True</font>;<br /> <font color="#008000">// Set other options of time series <br /> </font> r.KeepHistory := <font color="#008080">False</font>;<br /> r.HasEmptyAttribute := <font color="#008080">False</font>;<br /> r.HasMnemonics := <font color="#008080">False</font>;<br /> <font color="#008000">// Create dictionaries of facts, revisions, values<br /> </font> r.CreateRevisions;<br /> r.CreateFacts;<br /> r.CreateValues;<br /> <font color="#008000">// Create an attribute of the Country factors<br /> </font> CountryFactAttr := r.Facts.Attributes.Add(<font color="#008080">False</font>);<br /> CountryFactAttr.Id := <font color="#800000">"COUNTRY"</font>;<br /> CountryFactAttr.Name := <font color="#800000">"Country"</font>;<br /> CountryFactAttr.DataType := DbDataType.Integer;<br /> CountryFactAttr.Kind := (MetaAttributeKind.Dimension);<br /> CountryFactAttr.ValuesObject := mb.ItemByIdNamespace(<font color="#800000">"DICT_CTR"</font>, rds.Key);<br /> CountryFactAttr.Nullable := <font color="#008080">False</font>;<br /> <font color="#008080">If</font> r.Dimensions.FindByKey(CountryFactAttr.ValuesObject.Key) = <font color="#008080">Null</font> <font color="#008080">Then</font><br /> r.Dimensions.AddDimensionEx(CountryFactAttr.ValuesObject.Bind <font color="#008080">As</font> IDimensionModel);<br /> <font color="#008080">End</font> <font color="#008080">If</font>;<br /> <font color="#008000">// Create the Factor attributes of factors<br /> </font> IndicatorFactAttr := r.Facts.Attributes.Add(<font color="#008080">False</font>);<br /> IndicatorFactAttr.Id := <font color="#800000">"INDICATOR"</font>;<br /> IndicatorFactAttr.Name := <font color="#800000">"Factor"</font>;<br /> IndicatorFactAttr.DataType := DbDataType.Integer;<br /> IndicatorFactAttr.Kind := (MetaAttributeKind.Dimension);<br /> IndicatorFactAttr.ValuesObject := mb.ItemByIdNamespace(<font color="#800000">"DICT_IND"</font>, rds.Key);<br /> IndicatorFactAttr.Nullable := <font color="#008080">False</font>;<br /> <font color="#008080">If</font> r.Dimensions.FindByKey(IndicatorFactAttr.ValuesObject.Key) = <font color="#008080">Null</font> <font color="#008080">Then</font><br /> r.Dimensions.AddDimensionEx(IndicatorFactAttr.ValuesObject.Bind <font color="#008080">As</font> IDimensionModel);<br /> <font color="#008080">End</font> <font color="#008080">If</font>;<br /> r.CreateFacts;<br /> <font color="#008000">// Create primary index of time series DB<br /> </font> FactIndex := r.Facts.Indexes.Add;<br /> FactIndex.Primary := <font color="#008080">True</font>;<br /> FactIndex.Unique := <font color="#008080">True</font>;<br /> <font color="#008080">If</font> CountryFactAttr <> <font color="#008080">Null</font> <font color="#008080">Then</font><br /> FactIndex.Attributes.Add(CountryFactAttr);<br /> <font color="#008080">End</font> <font color="#008080">If</font>;<br /> <font color="#008080">If</font> IndicatorFactAttr <> <font color="#008080">Null</font> <font color="#008080">Then</font><br /> FactIndex.Attributes.Add(IndicatorFactAttr);<br /> <font color="#008080">End</font> <font color="#008080">If</font>;<br /> FactIndex.Attributes.Add(r.Facts.Attributes.FindById(<font color="#800000">"DL"</font>));<br /> <font color="#008000">// Specify query for the facts dictionary<br /> </font> fDict := r.Facts;<br /> fDict.UseQuery := <font color="#008080">True</font>;<br /> fDict.CompoundFactorKey := <font color="#008080">True</font>;<br /> s := <font color="#800000">"Select distinct country, indicator, dl, "</font> +<br /> <font color="#800000">"country *1000 + indicator as 'KEY', "</font> +<br /> <font color="#800000">"country *1000 + indicator as factor, "</font> +<br /> <font color="#800000">"1 as REV, 0 as DLT, cast(NULL as int) as OBT, "</font> +<br /> <font color="#800000">"cast(NULL as int) as UNIT from T_TSDB"</font>;<br /> Atts := fDict.Attributes;<br /> Atts.UseQuery := <font color="#008080">True</font>;<br /> Atts.SelectQueryText := s;<br /> <font color="#008000">// Specify query for revisions dictionary<br /> </font> rDict := r.Revisions;<br /> rDict.UseQuery := <font color="#008080">True</font>;<br /> s := <font color="#800000">"select 1 'KEY', 'SOURCE' NAM, current_timestamp DT, 0 KIN, "</font> +<br /> <font color="#800000">"'' USR, cast(NULL as int) CMT , 0 OBT"</font>;<br /> Atts := rDict.Attributes;<br /> Atts.UseQuery := <font color="#008080">True</font>;<br /> Atts.SelectQueryText := s;<br /> <font color="#008000">// Specify query for values dictionary<br /> </font> vDict := r.Values;<br /> vDict.UseQuery := <font color="#008080">True</font>;<br /> s := <font color="#800000">"Select country *100000 + indicator * 100 + "</font> +<br /> <font color="#800000">"datediff(dd,DT,{d'1980-01-01'}) 'KEY', "</font> +<br /> <font color="#800000">"country *1000 + indicator FACTOR, 1 REV, "</font> +<br /> <font color="#800000">"DL, value VL ,indicator, country, DT, -1 SC, "</font> +<br /> <font color="#800000">"CAST(NULL as NVarchar(255)) CMT, "</font> +<br /> <font color="#800000">"CAST(null as int) UNIT from T_TSDB"</font>;<br /> Atts := vDict.Attributes;<br /> Atts.UseQuery := <font color="#008080">True</font>;<br /> Atts.SelectQueryText := s;<br /> <font color="#008000">// Save time series DB<br /> </font> o.Save;<br /> <font color="#008080">End</font> <font color="#008080">Sub</font> UserProc;
Example execution result: a time series database that is based on the T_TSDB data table queries is created. This database contains custom attributes of the COUNTRY and INDICATOR factors that refer to the DICT_CTR and DICT_IND MDM dictionaries.
Executing the example requires that the repository contains the following:
Data table with the T_TSDB identifier that contains the following fields:
| Identifier | Field type | Comment |
| COUNTRY | Integer | Mandatory and included into the primary index. |
| INDICATOR | Integer | Mandatory and included into the primary index. |
| DL | Integer | Mandatory and included into the primary index. |
| VALUE | Real | Optional. |
| DT | Date | Optional. |
MDM repository with MDM_REPO identifier that contains the following:
The countries dictionary with the DICT_CTR identifier.
Factors dictionary with the DICT_IND identifier.
<font color="#008080">Imports</font> Prognoz.Platform.Interop.Cubes;<br /> <font color="#008080">Imports</font> Prognoz.Platform.Interop.Dal;<br /> <font color="#008080">Imports</font> Prognoz.Platform.Interop.Dimensions;<br /> <font color="#008080">Imports</font> Prognoz.Platform.Interop.Rds;<br /> …<br /> [STAThread]<br /> <font color="#008080">Public</font> <font color="#008080">Shared</font> <font color="#008080">Sub</font> Main(Params: StartParams);<br /> <font color="#008080">Var</font><br /> mb: IMetabase;<br /> ci: IMetabaseObjectCreateInfo;<br /> o: IMetabaseObject;<br /> r: IRubricator;<br /> rds: IMetabaseObjectDescriptor;<br /> CountryFactAttr, IndicatorFactAttr: IMetaAttribute;<br /> FactIndex: IMetaIndex;<br /> fDict, rDict, vDict: IMetaDictionary;<br /> s: String;<br /> Atts: IMetaAttributes;<br /> <font color="#008080">Begin</font><br /> mb := Params.Metabase;<br /> <font color="#008000">// Create time series DB<br /> </font> ci := mb.CreateCreateInfo();<br /> ci.ClassId := MetabaseObjectClass.KE_CLASS_RUBRICATOR <font color="#008080">As</font> integer;<br /> ci.Parent := mb.Root;<br /> ci.Name := <font color="#800000">"Time series DB on query"</font>;<br /> ci.Id := Mb.GenerateId(<font color="#800000">"RUB_QUERY"</font>, <font color="#008000">0</font>);<br /> o := mb.CreateObject(ci).Edit() <font color="#008080">As</font> IMetabaseObject;<br /> r := o <font color="#008080">As</font> IRubricator;<br /> <font color="#008000">// Set RDS repository<br /> </font> rds := mb.ItemById[<font color="#800000">"RDS_REPO"</font>];<br /> r.Database := rds.Bind() <font color="#008080">As</font> IRdsDatabase;<br /> <font color="#008000">// Specify, that the facts dictionaries,<br /> </font> <font color="#008000">// of values and revisions are based on the query<br /> </font> r.FactsOnQuery := <font color="#008080">True</font>;<br /> r.RevisionsOnQuery := <font color="#008080">True</font>;<br /> r.ValuesOnQuery := <font color="#008080">True</font>;<br /> <font color="#008000">// Time series have a compound key<br /> </font> r.CompoundFactorKey := <font color="#008080">True</font>;<br /> <font color="#008000">// Set other options of time series DB<br /> </font> r.KeepHistory := <font color="#008080">False</font>;<br /> r.HasEmptyAttribute := <font color="#008080">False</font>;<br /> r.HasMnemonics := <font color="#008080">False</font>;<br /> <font color="#008000">// Create dictionaries factors, of revisions, values<br /> </font> r.CreateRevisions();<br /> r.CreateFacts();<br /> r.CreateValues();<br /> <font color="#008000">// Create the Country factors attribute<br /> </font> CountryFactAttr := r.Facts.Attributes.Add(<font color="#008080">False</font>);<br /> CountryFactAttr.Id := <font color="#800000">"COUNTRY"</font>;<br /> CountryFactAttr.Name := <font color="#800000">"Country"</font>;<br /> CountryFactAttr.DataType := DbDataType.ddtInteger;<br /> CountryFactAttr.Kind := (MetaAttributeKind.meakDimension);<br /> CountryFactAttr.ValuesObject := mb.ItemByIdNamespace[<font color="#800000">"DICT_CTR"</font>, rds.Key];<br /> CountryFactAttr.Nullable := <font color="#008080">False</font>;<br /> <font color="#008080">If</font> r.Dimensions.FindByKey(CountryFactAttr.ValuesObject.Key) = <font color="#008080">Null</font> <font color="#008080">Then</font><br /> r.Dimensions.AddDimensionEx(CountryFactAttr.ValuesObject.Bind() <font color="#008080">As</font> IDimensionModel);<br /> <font color="#008080">End</font> <font color="#008080">If</font>;<br /> <font color="#008000">// Create the Factor attribute of factors<br /> </font> IndicatorFactAttr := r.Facts.Attributes.Add(<font color="#008080">False</font>);<br /> IndicatorFactAttr.Id := <font color="#800000">"INDICATOR"</font>;<br /> IndicatorFactAttr.Name := <font color="#800000">"Factor"</font>;<br /> IndicatorFactAttr.DataType := DbDataType.ddtInteger;<br /> IndicatorFactAttr.Kind := (MetaAttributeKind.meakDimension);<br /> IndicatorFactAttr.ValuesObject := mb.ItemByIdNamespace[<font color="#800000">"DICT_IND"</font>, rds.Key];<br /> IndicatorFactAttr.Nullable := <font color="#008080">False</font>;<br /> <font color="#008080">If</font> r.Dimensions.FindByKey(IndicatorFactAttr.ValuesObject.Key) = <font color="#008080">Null</font> <font color="#008080">Then</font><br /> r.Dimensions.AddDimensionEx(IndicatorFactAttr.ValuesObject.Bind() <font color="#008080">As</font> IDimensionModel);<br /> <font color="#008080">End</font> <font color="#008080">If</font>;<br /> r.CreateFacts();<br /> <font color="#008000">// Create primary index of time series DB<br /> </font> FactIndex := r.Facts.Indexes.Add();<br /> FactIndex.Primary := <font color="#008080">True</font>;<br /> FactIndex.Unique := <font color="#008080">True</font>;<br /> <font color="#008080">If</font> CountryFactAttr <> <font color="#008080">Null</font> <font color="#008080">Then</font><br /> FactIndex.Attributes.Add(CountryFactAttr);<br /> <font color="#008080">End</font> <font color="#008080">If</font>;<br /> <font color="#008080">If</font> IndicatorFactAttr <> <font color="#008080">Null</font> <font color="#008080">Then</font><br /> FactIndex.Attributes.Add(IndicatorFactAttr);<br /> <font color="#008080">End</font> <font color="#008080">If</font>;<br /> FactIndex.Attributes.Add(r.Facts.Attributes.FindById(<font color="#800000">"DL"</font>));<br /> <font color="#008000">// Specify query for facts dictionary<br /> </font> fDict := r.Facts;<br /> fDict.UseQuery := <font color="#008080">True</font>;<br /> fDict.CompoundFactorKey := <font color="#008080">True</font>;<br /> s := <font color="#800000">"Select distinct country, indicator, dl, "</font> +<br /> <font color="#800000">"country *1000 + indicator as 'KEY', "</font> +<br /> <font color="#800000">"country *1000 + indicator as factor, "</font> +<br /> <font color="#800000">"1 as REV, 0 as DLT, cast(NULL as int) as OBT, "</font> +<br /> <font color="#800000">"cast(NULL as int) as UNIT from T_TSDB"</font>;<br /> Atts := fDict.Attributes;<br /> Atts.UseQuery := <font color="#008080">True</font>;<br /> Atts.SelectQueryText := s;<br /> <font color="#008000">// Specify query for revision dictionary<br /> </font> rDict := r.Revisions;<br /> rDict.UseQuery := <font color="#008080">True</font>;<br /> s := <font color="#800000">"select 1 'KEY', 'SOURCE' NAM, current_timestamp DT, 0 KIN, "</font> +<br /> <font color="#800000">"'' USR, cast(NULL as int) CMT , 0 OBT"</font>;<br /> Atts := rDict.Attributes;<br /> Atts.UseQuery := <font color="#008080">True</font>;<br /> Atts.SelectQueryText := s;<br /> <font color="#008000">// Specify query for the dictionary<br /> </font> vDict := r.Values;<br /> vDict.UseQuery := <font color="#008080">True</font>;<br /> s := <font color="#800000">"Select country *100000 + indicator * 100 + "</font> +<br /> <font color="#800000">"datediff(dd,DT,{d'1980-01-01'}) 'KEY', "</font> +<br /> <font color="#800000">"country *1000 + indicator FACTOR, 1 REV, "</font> +<br /> <font color="#800000">"DL, value VL ,indicator, country, DT, -1 SC, "</font> +<br /> <font color="#800000">"CAST(NULL as NVarchar(255)) CMT, "</font> +<br /> <font color="#800000">"CAST(null as int) UNIT from T_TSDB"</font>;<br /> Atts := vDict.Attributes;<br /> Atts.UseQuery := <font color="#008080">True</font>;<br /> Atts.SelectQueryText := s;<br /> <font color="#008000">// Save time series DB<br /> </font> o.Save();<br /> <font color="#008080">End</font> <font color="#008080">Sub</font>;
Example execution result: a time series database that is based on the T_TSDB data table queries is created. This database contains custom attributes of the COUNTRY and INDICATOR factors that refer to the DICT_CTR and DICT_IND MDM dictionaries.
See also: