When we set out to build a better aggregation engine, we had two main goals. The first was to give control of the data back to the end-user by providing never-before-seen aggregation speed on top of a relational database. The second was to make the experience of using and administering the tool incredibly easy and intuitive. At every design decision, every line of code, and every discussion around functionality we have asked ourselves the same thing:
Does this make the user’s life easier?
We have leaned on a combined 25 years of experience in the EPM/CPM and analytic worlds to improve upon the frustrations of using other aggregation engines. Why, for instance, do some pivoting tools make you create incredibly complex statements in a proprietary language just to incorporate sign-based aggregations in a single dimension (much less multiple dimensions)? Why do some aggregation engines make you create three separate, distinct objects just to do a simple, redundant activity of clearing, loading, and aggregating data? Why is it so hard to get your data out of some aggregation engines?!?
There is, of course, a better way. A way that relies upon intuitive statements, easily understandable commands, and none of the bloat that is characteristic of other tools. A way that just makes sense.
To that end, we’d like to introduce Casascripts, the administration, calculation, and command language of Casabase. Casascripts are written in Excel or text files and executed and stored in Casabase. You can use them to:
- Create, modify, move, and delete dimensions, hierarchies, and members
- Import data, export data, copy data, and clear data
- Run calculations
- Create, modify, and delete rate groups
- Create, modify, and delete mappings
- And any other task available in Casabase
So what do Casascripts look like? Let’s take a look…
The Basic Casascript Format
Casascripts are written in either Excel or a text file. Every Casascript has similar syntax, a Casascript command followed by parameters associated with that command:
CasascriptCommand Parameter1 Parameter2 Parameter3 etc…
Here is an example of a Casascript to create a small P&L account hierarchy in the Account dimension:
The first thing you’ll notice is how easy it is to understand exactly what the Casascript is doing. Starting with line one, the Casascript engine will execute each line in sequence, building out the StoredAccount hierarchy (column C) in the Account dimension (column B). Column G represents the aggregation operator, and column I is the alias. Where other tools require two objects to load metadata (a file or data pull for the metadata and a separate rule object or process to interpret it), Casabase combines these into a simple and unambiguous Casascript that serves as both the metadata and rule to load it. You won’t have to wade through hundreds of options that you never use just to load up your metadata because Casabase focuses on simplicity and effectiveness to allow you to get the job done quickly.
The Casascript above is all it takes to create a complete, sign-based hierarchy to aggregate your data. Once this hierarchy (and any number of other hierarchies and alternate hierarchies in any number of dimensions) is created, simply load your data in at the lowest-level members. If you’ve spent years administering other aggregation tools, you might expect to create a clear script, a data load rule, and an aggregation script that runs every time you need to load data…three separate, distinct objects that must be created, maintained, updated, and kept in-sync to make sure the process runs correctly each time.
With Casascripts, it’s much easier:
Yep, that’s all that’s required to do a typical clear, load, and aggregation of data in Casabase. The ClearData Casascript command takes a POV as the first parameter (column B) and a POV to exclude from the clear as the second parameter (column C), so this particular ClearData command will clear all FY18 Actuals for every entity under TopEntity with the exception of anything under entity AE_02. Notice that TopEntity is an upper-level member; there is no need to explicitly tell Casabase you want to focus the clear on all bottom-level entities, this is implied since bottom-level members are the only place where data is stored in Casabase. There is no aggregated data to clear.
The LoadDataFile command simply takes the data stored in the first parameter (column B) and loads it to Casabase, with the second parameter (column C) indicating the delimiter and the third parameter (column D) indicating how many rows to skip.
Is your data stored in a relational source and not a file? No problem, simply change the above Casascript to utilize the LoadDataSQL command instead:
The first parameter (column B) is the data source that you are pulling data from (data sources are set up once by the administrator and can be utilized by every Casabase application), the second parameter (column C) is the SQL statement (or a path to a file with the SQL statement), and the third parameter (column D) is where load errors will be sent.
The last parameter (column E) is the name of a map set that will be applied to the data as it’s loaded in. That’s right, Casabase also has a robust and extremely fast mapping engine embedded! The mappings set up in the ACTMAP map set will be applied to the data prior to loading into Casabase. The loading process gives you up to 20 additional columns that can be utilized if your maps depend on data elements that are not present in the Casabase application dimensionality. All types of maps are available, from explicit, like, multi-dimensional, and SQL maps that can handle complex logic. (A larger discussion of mappings will come in a subsequent blog post.)
Notice there is no Casascript command to aggregate data in either of these Casascripts. You know the reason why by now…aggregation is done instantly and automatically when you request data at upper-level members!
Need to get your data out of Casabase? We know, this is a sore subject for many of you using other aggregation tools. Whether it’s dealing with strange syntax, slow running commands, or the fact that it’s simply impossible to do an export because of large dimensionality, other aggregation tools make doing something as simple as exporting data in a readable format an unnecessary burden for administrators. With Casabase, we think you’ll feel differently:
The ExportData Casascript command could not be easier to use. You can use it to export to either a file (line 1) or a relational table (line 2). Exporting to a relational table is extremely useful because the system keeps a log of every export that occurs, noting the time, POV, and user who exported the data, and the data is immediately available to other applications to query. This allows an easy way to snapshot data in the system and make it available as a backup or as a source for other reporting and analysis tools.
The results of exporting to a file are shown in the partial screenshot below:
Simple, useful, and resulting in an export ready for consumption by other processes or applications, exporting data from Casabase is a breeze!
Finally, Casascripts are easily accessible in the Casabase administration panel, where you can create, save, modify, and execute Casascripts:
There’s also a Casascript Builder that automatically creates Casascript lines in the correct syntax simply by filling out the necessary parameters:
Of course, this blog post barely scratches the surface of everything you can do with Casascripts. We will be launching a “How To…” blog series as we get closer to our production launch date that will go into detail about how to do various things in Casabase, and many of the posts will focus on Casascripts. The library of Casascript commands is constantly growing as we add new functionality to Casabase, so there will be no shortage of topics to cover!
Casabase Makes Your Life Easier
If you’re tired of struggling with your current tools to produce sign-based, aggregated data across multiple dimensions and hierarchies in an easy and timely manner, you may want to take a close look at all Casabase has to offer. The focus on simplicity and desire to make everything efficient and intuitive shows in every aspect of Casabase, and we think you’ll agree!