Using a SQL Data Block Range in Dodeca

One of the many reasons why Dodeca is the best tool on the planet for working with Essbase is how easily it integrates with your relational data.  It allows you to bring together the domains of both Essbase and relational data to create the exact system you need.

Often when people hear the words “Essbase” and “relational data” together they think of drill-through.  And yes, Dodeca makes it extremely easy to drill through to the relational source of your Essbase data.  But that is just tip of the iceberg.  Dodeca also provides an easy interface for WRITING back to your relational data systems.  This creates many more possibilities for your Planning, Reporting and Analysis systems that use Essbase.

There is some great information on the web describing the use of Relational data within Dodeca.  There are the Applied OLAP videos on their YouTube channel.  There are also some great blog posts by Tim Tow, Jason Jones, and others on this topic.

Here are some of great sources of SQL in Dodeca information:

Applied OLAP YouTube videos on Relational data in Dodeca

Jason Jones’ Primer on Relational Data Views in Dodeca

Tim Tow’s Fundamentals of SQL Writeback

Relational Drill-Through in Dodeca by Cameron Lackpour

Jason’s Relational Data Input in Dodeca

Jason’s Post on Dynamic Relational Grouping in Dodeca

Please note:  The post by Jason Jones on Relational Data Input does an excellent job of highlighting the game-changing nature of the ability to combine Essbase with relational data.  If this is an interest to you, and you haven’t read it already, I would urge you to have a look.

Into these waters I wade.  This blog will attempt to add to this fine collection of information with three posts on using SQL in Dodeca.  The first is a quick post on the SQL Data Block range.  The second will be a post on extending the functionality of the Data Block Range to validate SQL data before doing an insert.  The third will take a look using some Workbook scripting to actually cancel an SQL insert event if there are any validation issues.

Also note, I will not discuss the basics of setting up a SQL View in Dodeca.  Those basics are well covered in the information links above.

The SQL Data Block Range

The DataBlockSheetRangeName property

What exactly is a SQL Data Block Range?

There is a property in the DataTable Range Editor called DataBlockSheetRangeName.   In the editor, in the help section, the property is described as:

The name of an optional sheet range that contains the data range and additional columns that contain formulas that refer to cells within the data range.  The DataBlockSheetRangeName must be the name of a sheet-level defined range.

The data block range is used to define formulas within cells on a data row that refer to cells within the data range on the same row.  When the range is built and when rows are added, inserted, or deleted, the columns within the data block are maintained along with the data range. 

Simply put, this range defines formulas that can tag along with each row of data in the SQL data range.

For example, if you are retrieving monthly data in your SQL data range, you could sum the months into quarter and year values using formulas in the data block range.  If you brought back 3 rows of data or 3000 rows, the data block would follow along with the data range and calculate values for as many rows of data exist.

The data block range is an extension of the data range.  It contains the data range, plus additional columns.  See figures below.  The first is the sheet defined Data.Range and the second is the defined DataBlock.Range.  These are defined on the View Template in the View Template Designer.

The Data.Range defined on the View template
The DataBlock.Range defined on the View Template

You can see that the DataBlock.Range actually contains the Data.Range.

The SQL Passthrough Dataset for this View retrieves data for the first three months of the year.  These values will be entered in columns B, C, and D, as defined in the data range.  Column E is part of the data block and contains a formula to sum the months.

Formula defined within the DataBlock range

When the View is opened, we see that the data is retrieved, and the formula is there to sum the months for all the rows that are returned.

The View with SQL data returned, and with the DataBlock range to sum the data.

That’s it.  Using a Data Block range with your relational data in Dodeca comes in very handy.  In the next post, we will discuss using the Data Block range to do something a bit more sophisticated that adding columns together.




Leave a Reply

Your email address will not be published. Required fields are marked *