Migrating the SQL DemoDB to PostgresSQL

The installation media for Content Manager 9.2 comes with a demonstration dataset that can be used for testing and training.  Although I think the data within it is junk, it's been with the product for so long that I can't help but to continue using it.  To mount the dataset you have to restore a backup file onto a SQL Server and then register it within the Enterprise Studio.

SQL Server is a bit too expensive for my testing purposes, so I need to get this dataset into PostgresSQL.  In this post I'll show how I accomplished this.  The same approach could be taken for any migration between SQL and PostgresSQL.

I'm starting this post having already restored the DemoDB onto a SQL Server:

If you look at the connection details for the highlighted dataset, you'll see that GIS is enabled for this dataset.  My target environment will not support GIS.  This inhibits my ability to use the migrate feature when creating my new dataset.  If I tried to migrate it directly to my target environment I would receive the error message shown below.

Even if I try to migrate from SQL to SQL, I can't migrate unless GIS is retained...

To use the migration feature I need to first have a dataset that does not support GIS.  I'll use the export feature of the GIS enabled dataset to give me something I can work with.  Then I'll import that into a blank dataset without GIS enabled.

The first export will be to SQL Server, but without GIS enabled.  When prompted I just need to provide a location for the exported script & data.

Once completed I then created a new dataset.  This dataset was not initialized with any data, nor was GIS enabled. The screenshot below details the important dataset properties to be configured during creation.

After it was created I can see both datasets within the Enterprise Studio, as shown below.

Next I switched over to SQL Server Management Studio and opened the script generated as part of the export of the DemoDB.  I then executed the script within the database used for the newly created DemoDB No GIS.  This populates the empty dataset with all of the data from the original DemoDB.  I will lose all of the GIS data, but that's ok with me. 

Now I can create a new dataset on my workgroup server.  During it's creation I must specify a bulk loading path.  It's used in the same manner as the export process used in the first few steps.  The migration actually first performs an export and then imports those files, just like I did in SQL Server.  

On the last step of the creation wizard I can select my DemoDB No GIS dataset, as shown below.

Now the Enterprise Studio shows me all three datasets.

PostgresSQL in GCP

If you're like me and are moving to the GCP stack, you'll find you have a few extra steps to undertake when preparing your infrastructure. 

First, you'll have to manually install the PostgresSQL ODBC drivers on your VM instance.  You can download them from the PostgresSQL website, locate here: 

https://www.postgresql.org/ftp/odbc/versions/

Second, you create your PostgresSQL instance within your GCP project.  This was super simple.... click, click, click, done.

Next I secured access between my PostgresSQL instance and the VM instance.  After that, it is necessary to create a schema for your Content Manager database.  Content Manager will throw up on the screen if you don't have one already prepared.  

To resolve the issue you'll need to manually create your schema within the database.  I did this via the gcloud shell, but you could do this locally from your gcloud SDK (or any database management tool capable of managing a PostgresSQL instance).

Only draw-back to this cloud instance of PostgresSQL is there is no GIS support...

Now that my Content Manager instance is fully in the cloud I can leverage all of the really cool features of the GCP stack!

Fiscal Calendar Fun

A collection of SQL statements to be used as logical tables in support of fiscal calendar transformations with dates stored as integers.  Will need these to be able to report metrics out of CM based on an internal fiscal calendar.  

---

Current Fiscal Day, Month, and Year with Start/End & Yesterday

SELECT 
	fiscal_month_year_nr, 
	fiscal_year_nr, 
	fiscal_month_nr, 
	fiscal_month_year_bgn, 
	fiscal_month_year_end, 
	fiscal_year_bgn, 
	fiscal_year_end, 
	cast(convert(varchar(8),dateadd(d,-1,getdate()),112) as int) yesterday 
FROM Dim_Fiscal_Month_Year 
WHERE 
	fiscal_month_year_bgn <= cast(convert(varchar(8),dateadd(d,-1,getdate()),112) as int) 
	AND 
	fiscal_month_year_end >= cast(convert(varchar(8),dateadd(d,-1,getdate()),112) as int)

Fiscal Week to Date

select d.[date_id], wtd.[Date_id] wtd_fiscal_date_id
from [Dim_Date] dd
join [Dim_Fiscal_week_Year] fwy
	on (dd.[Fiscal_week_year_nr] = fwy.[fiscal_week_year_nr])
join [Dim_Date] wtd
	on (wtd.Date_id between fwy.[fiscal_week_year_bgn] and dd.[date_id])

Fiscal Month to Date

select dd.[date_id], mtd.[Date_id] mtd_date_id
from [Dim_Date] dd
join [Dim_Fiscal_Month_Year] fmy
	on (dd.[Fiscal_month_year_nr] = fmy.[fiscal_month_year_nr])
join [Dim_Date] mtd
	on (mtd.Date_id between fmy.[fiscal_month_year_bgn] and dd.[date_id])

Fiscal Quarter to Date

select dd.[date_id], qtd.[Date_id] qtd_date_id
from [Dim_Date] dd
join [Dim_Fiscal_Quarter_Year] fqy
    on (dd.[Fiscal_quarter_year_nr] = fqy.[fiscal_quarter_year_nr])
join [Dim_Date] qtd
	on (qtd.Date_id between fqy.[fiscal_quarter_year_bgn] and dd.[date_id])

Fiscal Year to Date

select dd.[date_id], ytd.[Date_id] ytd_date_id
from [Dim_Date] dd
join [Dim_Fiscal_Year] fy
	on (dd.[Fiscal_year_nr] = fy.[fiscal_year_nr])
join [Dim_Date] ytd
	on (ytd.Date_id between fy.[fiscal_year_bgn] and dd.[date_id])

Prior Fiscal Year Day

The trick here is to calculate the number of days since the start of the year (for the given date) and then add that number to the start date of the prior fiscal year.

select d.[Date_id], pyd.Date_id pydate_id 
from [Dim_Date] d 
join [Dim_Fiscal_Year] fy 
	on d.Fiscal_year_nr = fy.fiscal_year_nr 
join [Dim_Fiscal_Year] py 
	on (py.fiscal_year_ix = (fy.fiscal_year_ix-1)) 
join [Dim_Date] pyd 
	on (pyd.Calendar_dt = DATEADD(D,Datediff(D,convert(date,CONVERT(varchar(10),fy.fiscal_year_bgn,101)), d.Calendar_dt),convert(date,CONVERT(varchar(10),py.fiscal_year_bgn,101))))
where fy.fiscal_year_bgn not in (-1,0)