I didn’t know what (DTS) time it was, Then I met you
What is Dynamic Time Series (DTS)? I am ever so glad you asked ‘cos if you didn’t, there wouldn’t be much to this post, would there? To quote (and illustrate) from the Essbase Database Administrator’s Guide (oooooh, the very latest 19c one out in the cloud). Note that it is not entirely accurate.
Just what is DTS? Again, per the DBAG:
What this means is that you can calculate all sorts of period-to-date members in BSO without having to write code or do math in Excel or a standard report. Lovely.
Oh, what a (non)lovely time it was, How (un)sublime it was too
I’m here to tell you a (small) secret. This functionality wasn’t always there in Essbase SaaS. In the very early days of this supercalifragilisticexpialidocious product, It Wasn’t There. Ugh. But that is the past.
I didn’t know what day it was, You held my hand, Warm like the month of May it was, And I’ll say it was grand
The present is rather different – DTS is fully supported and it’s super easy to set up just as it is in the on-premises Essbase that we all know and love.
Edit the dimension, and select the Dynamic Time Series button:
Perform a database refresh:
Grand to be alive, to be young-ish, To be mad (so some say), to be yours alone, Grand to see your dynamically calculated period to date face, feel your touch, Hear your voice say, “I’m all your own”
Simply type in Q-T-D(May) in the same column (or row if Year were across columns):
There we go – a dynamic quarter to date.
I didn’t know what dynamically calculated period to date month it was, Life was no prize, I wanted DTS dates and here it was, Shining out of your pod, I’m wise and I know what time it is now
That was QTD. Let’s try YTD. Set it and refresh:
Whoopsie. This is the same issue in “real” Essbase, btw.
So now I’m off to rename Sample.Basic’s Year dimension to Years.
Changed it and set the Y-T-D member again:
Oh dear, this seems to be my day for whoopsies. Why would anyone set a DTS to the same generation as another? Even Yr. Obt., Svt., & Mst. Hmbl. Svt. doesn’t know but then again he does things that frequently surprise him. No matter, set it to 1 (note that I don’t have to refresh it to get the Year to Years member name right as this is still in the relational layer), refresh again, go off into Excel and Smart View and:
That’s it. I don’t know if all and sundry use DTS in BSO cubes but really they ought to because it’s so valuable and so easy.
As this shows, if it’s in Essbase, it’s in Essbase SaaS. Again and again and again. Just as it should be.
Be seeing you.
2 thoughts on “A Free Form Planning aka Essbase SaaS Journey — part 7, I didn’t know what (DTS) time it was”
The only problem with the built in DTS is that it does not always play nice with other calculations. DTS always executes last so if you need to sum up members before your calculation you are out of luck. What you didn’t mention is DTS does honor time balanced members so that is very good
It’s all of the pleasures and the pains of Essbase but as SaaS.
I sure do wish they’d fix that whole Year/Years DTS issue but it’s been like since the Year Dot.