Execute a truncate statement from Scribe


Well this has nothing to do with EPM, hmmm not that is not 100% true.

I was doing a project which involved Microsoft Dynamics and Essbase. The idea is to pull data from CRM to an on-premises Essbase cube.

The integration tool the company already owns is Scribe. I had to get stuff done ASAP.

The main challenge was to clear the tables before loading them using Scribe Solutions.

One way is to run a scheduled job on-premises before the Scribe job and truncate the tables.
Next one is to have a map (oh I’ll talk about maps) which runs a truncate statement.

Well I don’t know I was stuck with the idea of doing it the second way. (Maybe I didn’t like giving up ;))

Either my Google-fu is bad all the links were talking about placing the SQL in a location and calling that #@#$%#@$%.

To be honest first of all I didn’t have a clue what they were talking about and second of all I didn’t like it.

Maps in scribe is a cool visual way (I like it, it is simple) to get data from source to target. you can define which table you want to get data from, join to another table and perform mapping to target tables.

Oh I forgot to mention, when you try to join, it will show you only the tables which you can create a join. Once you select the table, it’ll then populate which fields can be used as a join.

So let’s look at a map shall we.

I call it making a burger 🙂

First you query the source, then you can decide what you want which each result, then create, update, delete,…. source table.

If you keep the names the same (source columns same as target columns) it can perform an autolink for you!!!!

That’s about the map, create it and execute the map, data from cloud will be sitting in your on-premises SQL server in no time.

My challenge was to perform a truncate operation before running the data pull.

As you can see I’ve two maps in my solution.

  1. Truncate map
  2. Data pull map
Let’s look at the truncate map
Only source is defined, and as you can see since my source is SQL server, I get a Native Query (bun ;))
So what happens when you write a truncate statement now, Scribe will start complaining that the query returned a null schema table.
Hmm now how to overcome that. Easy peesy 
🙂 yup that’s what I did. Just return current data and alias it to a column.
There you go a much easier way to run Native SQL queries in Scribe.
Advertisements

About Celvin Kattookaran

I’m an EPM Consultant, my primary focus is on Hyperion Planning and Essbase. Some of you from Hyperion Support team might recognize me or have seen my support articles, I was with the WebAnalysis Support Team. I'm an Independent Consultant with “Intekgrate Corporation” based out of Aurora office. I’m from God’s Own Country (Kerala, India), lived in all southern states of India, Istanbul and Johannesburg (and of course United States). I’m core gamer :) and an avid reader. I was awarded Oracle ACE Director for my contributions towards EPM community.

Leave a Reply