Dodeca Commentary Part Two

This post is part two of three in a series on using commentary in Dodeca. In this post I will be showing how to set up a view that shows all the comments entered for an Essbase cube.

This report combines Essbase and relational data so I set it up as an Essbase Excel View.

The template is fairly straight forward.

 

I added a cell that will contain my ConnectionID as well as the scenarios I will use to retrieve Essbase data.

I set up four named ranges.

 

One range will define the column that will contain the comments.

 

The next range will tell Dodeca where the Essbase retrieve range will begine.

 

The Header.Row.1 range defines where the Essbase scenarios will be stored.

 

The SQL.DataRange.1 will tell Dodeca where to bring in the relational data.

In order to get the zebra striping on this report, I did export the template to Excel. I set up a conditional formatting rule to handle it. In this case I used the ISODD() and ISEVEN() Excel functions along with the ROW() function. This is a simple way to add this type of formatting to a view. Once I had set this conditional formatting up, I imported the template in Dodeca.

Next I created a SQL Passthrough Dataset and defined one query to bring back the comments.

The query I set up will only perform a Select statement.

This is the SQL used to bring back all the comments in the current Tenant with the ConnectionID for the cube.

 

This SQL is a bit tricky since each Key,Value pair in the Comment_Key_Items table is stored as a row. Essentially the rows need to be pivoted to the columns -- something not very straightforward in SQL.

 

If you do end up trying to reproduce this view in Dodeca and have trouble writing the SQL, drop me a note in the comments and I'll be glad to give you a hand with your specific dimensionality. There are several ways to write the SQL -- this is just one of many.

 

I added ConnectionIDs as a Key,Value pair just in case the dimensionality in this cube is the same as another cube using commentary. This practice will help to avoid any unintended conflicts.

The only view property of note in this case is DataSetRanges.

I set the SQLPassthroughDataSetID to PaloMartComments which I created in the prior step.

In the DataTable Range Editor I turned on AutoFiltering which will allow us to sort and filter on any of the fields in the view.

 

I also changed SetDataFlags to InsertCells, AllText. This will expand the named range when the SQL data is returned. So any formatting will be copied throughout the range.

Next I set two properties.

 

The first I called OpenedFromPaloVarianceComments. I'll use this in the next post when I create the adhoc sheet based upon a double-click. I set that value to true and allowed it to be shared between views.

 

The next I called IsDoneBuilding. I set this to false. I will use this to handle unwanted Essbase retrieves after the initial pull of data.

I ended up creating procedures that run upon four different Events being fired. I'll discuss all but the CellDoubleClicked. I'll handle that in part three of this series.

I used the AfterWorkbookOpen event to add a couple of tokens to the view.

I added one token for the Tenant. This was used when I set up the SQL query.

I also added a token for the ConnectionID. This was used in the SQL query and was placed on the sheet.

The first method in the OnAfterDataSetRangesBuild procedure is InsertRange. This will add a row at the top of the returned comments. This procedure gets called after the SQL query is run and the data is placed on the sheet.

The next method is CopyRange. I used this to copy in our Header.Row.1 named range to the empty line I just added. This will copy in our Essbase scenarios.

Once this is complete, we now have a well formed Essbase retrieval range with five of the dimensions in the rows and the Scenario dimension in the columns.

Now that we have a well formed Essbase retrieval range, we can create the defined range name by using the AddDefinedName method. In this case I'm using the Offset function along with the SQL.DataRange.1 named range to create the new range.

Once I had created an Essbase retrieve range all I had to do was perform the EssbaseRetrieve method using a RetrievePolicy of RetrieveRanges to get my data back.

In this view I only needed the scenario names at the top of my sheet for the Essbase retrieve. Once that was done, I no longer needed them. Hiding them wasn't a great option since they could possibly be unhidden during a sort or filter change. Deleting them was the easiest way to remove them.

Next I set the row height so multiple lines of comments were visible. I decided to show three lines of comments for this report.

Next I added some borders to make things a little easier to read.

And some more borders.

Next I set column widths to autofit the SQL.DataRange less a few hidden columns.

I wanted to make the comments column a bit wider so I added another SetColumnWidth method.

I wasn't happy with the way autofit handled some of the columns. It looks like the filter down-arrows don't count when it does the fitting. So I used the @ColWidth Dodeca function to increase each field by '4' which made the sheet look much cleaner and all headers readable. I also only ran the method on cells with a ColWidth not zero. That eliminated any hidden rows from being expanded.

Next I set the IsDoneBuilding property to true. No data needs to be retrieved after the initial build on this view. So I'll use this to cancel the BeforeSheetRetrieve event.

Finally, I created the OnBeforeSheetRetrieve procedure to check if the view is done building. If it is done, I cancel the event. I use the @PVal Dodeca function to check the IsDoneBuilding property value. Adding this method stops the view from updating unnecessarily. After the initial build, the only thing a user can do is sort and filter the data.

 

In the next step, I'll show how to use the double-click event to take the user to an adhoc sheet.

Dodeca Commentary Part One

This post is part one of three in a series on using commentary in Dodeca. In this post I will be showing how to set up a simple view that allows user comments.

First I set up the view template to include an Essbase retrieve range.

 

I then added a column for users to enter their comments. I chose Column F for this example.

Next I created three named ranges in the template.

 

Comments.Range is the area in which users will enter their comments.

 

ConnectionID will hold the ConnectionID for the view and will be hidden from the end user.

 

Ess.Retrieve.Range.1 is the range into which Essbase data will be retrieved.

The next part involves writing an Excel formula so that Dodeca knows how the commentary data should be stored in the repository.

 

Dodeca uses Key,Value pairs in order to store the comments.

 

For example, "Years" would be a key while "2019" would be the value. You can add as many or few Key,Value pairs together as you want to represent a single comment. In this case I'm going to add a Key,Value pair for each dimension in addition a Key,Value pair for my view's ConnectionID. That way comments from this cube will not interfere with comments from another cube if the dimensionality is identical.

The string related to Years for 2019 should look like this:

Years=2019;

This Key,Value pair will be concatenated to the rest of the Key,Value pairs that make up an entry. The semi-colon is used as a delimiter.

 

That formula is pasted into cell G9 and copied down the sheet. The formula will pick up the correct value for each key from the view.

 

Column G will eventually be hidden from the user. The user needs no understanding of how Dodeca manages comments.

Next I hid rows and columns, headings and gridlines.

In my view properties, I selected the ellipses on the CommentRanges property.

Next I clicked "Add".

 

Then I set various properties for my comment range. Notice the properties in bold have been changed from the default value.

 

Note two critical properties on this screen. The first is Address. This is where I entered the named range from my view template. This is the range in which the user will be entering their comments.

The next critical property is KeyItemsString. This tells Dodeca where to look for the Key,Value pair string related to a given comment. In this case I'm using the Offset() Excel function to find the cell immediate the right.

 

 

Finally, I did add one method in a Workbook Script. This handled putting the ConnectionID on the view so it could be used in a Key,Value Pair.

I added the @ViewPVal("EssbaseConnectionID") function in the SetEntry method. It populates the ConnectionID named range I set up in an earlier step.

Now when I run the view, I can enter comments in the Variance Explanation column. Once I type a comment and press enter, the comment is immediately saved to the Dodeca repository.

 

In Part Two, I will show how to create a view that shows all the comments for a cube on a single screen.

Developing Essbase Applications: Hybrid Techniques and Practices

Book Review

Developing Essbase Applications: Hybrid Techniques and Practices

I had a colleague many years ago who always talked about writing a book about Essbase. It never materialized of course, because it's really hard to do. First of all there's the most excellent DBA Guide reference which tells you most of what there is to know. Second, the developer community is tiny in comparison to technologies like Java, C, SQL Server, etc. So kudos go to Cameron Lackpour and his co-authors on a writing a second book on the subject of Essbase.

The book is made up of eight diverse chapters on subjects in the Essbase universe. They are able to be separated out so I won't review the book as a whole but rather will give my thoughts on each individual chapter. One major item to note is that the title is a bit misleading. "Hybrid Techniques and Practices" is not really the focus of the book but instead of one of the chapters.

Essbase on Exalytics

Without hardware, software is not possible. Developers tend to take hardware for granted and sometimes don't pay attention to it unless there's a problem -- or unless they're looking for something to blame. This chapter brings the discussion of hardware to the forefront and does a very good job of explaining who needs to buy an Exalytics box and why. John shows how to configure an Exalytics machine so you're taking advantage of all that power. This chapter is required reading for anyone putting together an Exalytics business case or implementing a solution on that platform for the first time.

Hybrid Essbase

In this chapter we get a break down of the latest Essbase hot topic. This should be required reading before even thinking about implementing Hybrid as Tim and Cameron are very upfront about the current short comings of Hybrid. The chapter gives a very good overview of how Hybrid probably works under the covers and for that I'm very thankful. If I had to voice one criticism of this chapter it would be that it was written prematurely. I remember ASO coming along and not touching it for several years. In my opinion, it wasn't ready to be used in most Essbase shops as the initial limitations were overwhelming. People still used it and perhaps that's why it got better. Hopefully this chapter will help to accelerate development at Oracle on this new technology by pointing out the, in my opinion, glaring weaknesses it currently has. It is my hope that this chapter will be updated sooner rather than later in some form or fashion showing how capable Hybrid has become and how the limitations have disappeared.

The Young Person's Guide to Essbase

I'm not sure if the name of this chapter is appropriate as it seems geared more toward someone with little Essbase experience rather than a young person. This is twenty years of Essbase wisdom boiled down to its essence and condensed into a single chapter. This is the chapter you give to an IT Project Manager who is helping to implement Essbase for the first time. I wish that I could have given this to several clients and colleagues over the years. It would have saved many hours of explanation.

Essbase Performance and Load Testing

I know that I need to do performance and load testing but it always seems to be the first thing skimped on during a project. This chapter is useful to understand the conceptual challenges with designing a quality data set for Essbase testing. It would be very useful for a testing analyst with little Essbase experience to read before embarking on the testing phase in an Essbase implementation. I'll be stealing using some of the ideas in this chapter when comparing the performance of different Essbase techniques in the future. A tip of the cap for including some Perl code in the chapter.

Utilizing SQL

I love pulling data from SQL when creating Essbase cubes. It usually allows me to focus on design rather than messing around with file formatting, transferring and such. Not every shop wants to use it -- some only want to deal with flat files. As a result I seem to go from a project that uses it to another that doesn't and I quickly forget everything I knew. I scramble to do searches on all of the basic SQL commands to re-familiarize myself each time. Glenn does an excellent job in giving examples for about 95% of the SQL you will ever need to use during an Essbase project. This is the chapter you'll want to keep handy when starting a new project that uses relational tables. There's no question in my mind that this will save me time in the future.

Copernicus Was Right

As the author points out, "This chapter is intended, primarily, for the Essbase consultants who are starting to work with OBIEE." I have no doubt this chapter will serve as a valuable primer on the subject for those who find themselves in that position. If your company is evaluating whether or not to integrate Essbase and OBIEE or you're just looking at ways to improve the marriage of your summary and transaction level data then this chapter should prove useful.

Managing Spreadsheets through Dodeca

Dodeca is an Essbase front-end from Applied OLAP. If you're a Dodeca user then you'll want to read this chapter. If you're looking for a way to control, audit and manage spreadsheets in your organization, you'll want to check this product out. I have not worked in an organization that uses Dodeca but I've heard many testimonials from customers that love it. I've also corresponded with Applied OLAP owner Tim Tow on Essbase technical forums for the past 15+ years. He does quality work and is extremely responsive to customer needs.

Smart View Your Way

This chapter scared me a little. Reading it will take you from being an experienced Smart View user to an expert. I'm hesitant to recommend this to an analyst because it might just create a monster. I really hate getting called in to fix complicated spreadsheets with VBA code and I'm afraid urging someone to read this chapter might give them the wrong idea (although to be fair, warnings against such recklessness are included). Nevertheless, there are a lot of valuable techniques and information included. As I am not currently in the role of an analyst, I use Smart View to do some occasional data analysis and number checking. Share it with your users at your own risk.

Conclusion

If you're an Essbase developer, buy the book. Ideas and techniques in it will be referenced in online forums and in conferences for the next few years at least. Also, don't ignore the book if you're skeptical of the Hybrid Storage Option. By the same token, don't expect a book solely focus on Hybrid.