Robert Ensinger

Hello DataWarehouse!

Blog Post created by Robert Ensinger on Jan 6, 2017

My life has been transformed since I've embraced a LEAN-Agile mindset. I used to be a lazy procrastinator. Now I Maximize the Work Not Done until the Last Responsible Moment.

 

I'm working on a GEL script that will require me to build & shape data out of Timeslices. This sounds a lot like work. Is there an easier way? Wait a second... that DataWarehouse has all that data normalized right there for the pickin'! A wise man once said to me "What if we just try easy?"

 

We're a small implementation. We've never had another datasource to pull data from before so I've never thought about where I can most easily pull data from. It's a quiet Friday - let's run a Spike.

 

First, let's say Hello to our PPM Database. Notice the dbId of niku below. This corresponds to the PPM dbId in our properties.xml.

<gel:script xmlns:core="jelly:core" xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary" xmlns:sql="jelly:sql">
<gel:setDataSource dbId="niku"/>
<sql:query var="data" escapeText="0">
            <![CDATA[SELECT
            DB_NAME() NAME
         , PPM.BASE_RELEASE
         , PPM.INSTALLED_VERSION
         , PPM.INSTALLED_DATE
         FROM CMN_INSTALL_HISTORY PPM
         WHERE PPM.INSTALLED_DATE = (SELECT MAX(INSTALLED_DATE) FROM CMN_INSTALL_HISTORY)]]>

</sql:query>
<core:set value="${data.rows[0].NAME}" var="name"/>
<core:set value="${data.rows[0].BASE_RELEASE}" var="base_release"/>
<core:set value="${data.rows[0].INSTALLED_VERSION}" var="installed_version"/>
<gel:out>Hello PPM DB:${name}  Base Release:${base_release}  Installed Version:${installed_version}!</gel:out>
</gel:script>

 

How you doin' PPM?

 

OK. Success. Now let's try the DataWarehouse. Notice the dbId of Datawarehouse below. This corresponds to the Data Warehouse dbId in our properties.xml.

<gel:script xmlns:core="jelly:core" xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary" xmlns:sql="jelly:sql">
<gel:setDataSource dbId="Datawarehouse"/>
<sql:query var="data" escapeText="0">
            <![CDATA[SELECT
                 DB_NAME() NAME
      , DWH.BASE_RELEASE
      , DWH.INSTALLED_VERSION
      , DWH.INSTALLED_DATE
      FROM CMN_DWH_INSTALL_HISTORY DWH
      WHERE DWH.INSTALLED_DATE = (SELECT MAX(INSTALLED_DATE) FROM CMN_DWH_INSTALL_HISTORY)]]>

</sql:query>
<core:set value="${data.rows[0].NAME}" var="name"/>
<core:set value="${data.rows[0].BASE_RELEASE}" var="base_release"/>
<core:set value="${data.rows[0].INSTALLED_VERSION}" var="installed_version"/>
<gel:out>Hello DataWarehouse DB:${name}  Base Release:${base_release}  Installed Version:${installed_version}!</gel:out>
</gel:script>

 

How goes it DWH?

 

Fantastic. Now let's combine 'em both.

<gel:script xmlns:core="jelly:core" xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary" xmlns:sql="jelly:sql">
<gel:setDataSource dbId="niku"/>
<sql:query var="ppm" escapeText="0">
            <![CDATA[SELECT
            DB_NAME() PPM_DB_NAME
         , PPM.BASE_RELEASE PPM_BASE_RELEASE
         , PPM.INSTALLED_VERSION PPM_INSTALLED_VERSION
         , PPM.INSTALLED_DATE PPM_INSTALLED_DATE
         FROM CMN_INSTALL_HISTORY PPM
         WHERE PPM.INSTALLED_DATE = (SELECT MAX(INSTALLED_DATE) FROM CMN_INSTALL_HISTORY)]]>

</sql:query>
<core:set value="${ppm.rows[0].PPM_DB_NAME}" var="ppm_db_name"/>
<core:set value="${ppm.rows[0].PPM_BASE_RELEASE}" var="ppm_base_release"/>
<core:set value="${ppm.rows[0].PPM_INSTALLED_VERSION}" var="ppm_installed_version"/>

<gel:setDataSource dbId="Datawarehouse"/>
<sql:query var="dwh" escapeText="0">
            <![CDATA[SELECT
                 DB_NAME() DWH_DB_NAME
      , DWH.BASE_RELEASE DWH_BASE_RELEASE
      , DWH.INSTALLED_VERSION DWH_INSTALLED_VERSION
      , DWH.INSTALLED_DATE DWH_INSTALLED_DATE
      FROM CMN_DWH_INSTALL_HISTORY DWH
      WHERE DWH.INSTALLED_DATE = (SELECT MAX(INSTALLED_DATE) FROM CMN_DWH_INSTALL_HISTORY)]]>

</sql:query>
<core:set value="${dwh.rows[0].DWH_DB_NAME}" var="dwh_db_name"/>
<core:set value="${dwh.rows[0].DWH_BASE_RELEASE}" var="dwh_base_release"/>
<core:set value="${dwh.rows[0].DWH_INSTALLED_VERSION}" var="dwh_installed_version"/>

<gel:out>Hello PPM DB:${ppm_db_name}  Base Release:${ppm_base_release}  Installed Version:${ppm_installed_version} and DWH DB:${dwh_db_name}  Base Release:${dwh_base_release}  Installed Version:${dwh_installed_version}!</gel:out>
</gel:script>

 

Brilliant.

 

Just because one can doesn't mean one should. What are the risks? The DWH load job fails and when my GEL needs to run there's no data? Connectivity Issues? Are these really any greater with the DWH than the PPM DB? Can I build in robust error handling and safeguards?

 

Time to take a step back and think. It's good to know one can. Hello DataWarehouse. What if we just try easy?

Outcomes