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.
- Truncate map
- Data pull map
