Get CurrentPeriod value for each model inside a model site from Planning DB

While most of Planning system’s meta data is stored in native relational tables in the Planning database, there are some meta data is stored in xml format in the database, for example, Current Period for each model inside a specific model site. You may need to get CurrentPeriod value for the model from the Planning databases. Here is a sample SQL query to get the CurrentPeriod for each model inside a specific model site. The following example uses model site label of “Corporate”. Please note that this is only valid for the current released version of Planning system, the future release may change the way how these meta data is stored.

DECLARE

@xmlblob xml

SELECT

@xmlblob = CAST(CAST ( ba.xmlblob AS varbinary(MAX)) as xml)

FROM

BizAppNodes ba

WHERE

VersionEndDateTime = '9999-12-31 00:00:00.000'

AND

BizAppNodeLabel = 'Corporate'

SELECT

DISTINCT

tab.col.value('../../@Label', 'varchar(30)') as ModelLabel,

tab

.col.value('@CurrentPeriodId', 'varchar(30)') as CurrentPeriodId

FROM

@xmlblob.nodes ('/BizModelSite/Models/ArrayOfBizModel/BizModel/EffectiveDatedCurrentPeriods/EffectiveDatedCurrentPeriod') as tab(col)