Glitch with a non-printing Unicode character in member name

This is an Essbase bug, kind of. I’ve been working on a project lately that uses the relatively new MaxL Essbase outline export command (yes, Pete, I called it relatively new again, even though according to you it’s not… well, it’s relatively NEW TO ME SO THAT’S WHAT MATTERS… :-). Anyway, I wrote a quick XML parser for the output, using Java.

The nice thing about the parser is that it uses something in the Java language called JAXB. It’s a really nice way of modeling the contents of an XML file using Java classes, so that you don’t have to write your own parsing code, which is tedious and error prone. There are reasons you might use either approach, but overall I have been very happy over the last few years with the ability to write XML readers in Java with JAXB.

Curiously, I came across an outline export that would cause the parser to throw an exception. The Java stack trace indicated that an illegal character (0x1f – that’s not the character itself, rather, the Unicode character ID) was at fault. Specifically, character 0x1f is the “unit separator” character. In a nutshell you might say that while most of us are used to writing things with letters and numbers and things like tabs, spaces, and newlines, there happen to be all of these other weird characters that exist that have arcane uses or historical reasons for existing. It’s such a prevalent issue (or at least, can be) that many advanced text editors have various commands to “show invisibles” or non-printing characters. One such tool that many of us Essbase boffins are adept with is Notepad++ – a veritable Swiss army knife of a text editor.

Nicely enough, the Java stack trace indicated that the problem in the XML was with parsing a “name” attribute on a <Member> tag – in other words, an Essbase member name in the source outline contained an invisible character. As it turns out, in XML 1.0 it is illegal to have this particular character. So while Essbase happily generates invalid XML during the export, when I try to import it with Java, I get the exception. But how to find the offending member? I mean, how do you do a text search for an invisible character (seriously, this is like some “what is the sound of one hand clapping” kind of stuff).

In Notepad++ you can search for a regular expression. So I turned on Show Invisibles, pulled up the Find dialog, checked on the “Use Regular Expressions” option, then typed in [\x1f] which is is the Regex code to tell Notepad++ to search for this little bastard of a character. Sure enough, there was exactly one in the output file that surely snuck in from an otherwise innocuous copy and paste to EAS some time ago. I fixed the member name in EAS, reran the export, reprocessed with the parser, and all was well again in the universe.

Drillbridge White Paper now available

I’m crossing another bucket list to-do item off of my list: writing my first white paper. And what’s it on? Drillbridge, of course. I’ll be making it available to a wider audience next week, but if anyone is interested in a concise, succinct paper on how Drillbridge works, and how it can be used to quickly implement drill-through with a minimum of resources, please email me and I’ll send it right over. I’d love to get your thoughts.

cubus outperform EV Analytics Review: Position in the Enterprise

Welcome to the fourth installment of my increasingly inaccurate EV three-part review. If you missed the first three parts, you can check out the EV background, using EV, and using EV continued parts to catch up!

I hope you enjoyed this little mini-series on a really interesting tool so far. As I mentioned in the first article, this tool has a special place in my heart owing to how critical it was to my Essbase life going back as far as 2005. I was quite the EV enthusiast back in the day within my company, but when I talked to people about trying it out or using it, it quite often fell flat. I’d often hear, “Why don’t I use the Excel add-in?” or “Why do I need another tool for that, I already have [whatever]” or “isn’t the enterprise standard [some enterprisey thing]?”

I see where these people were coming from. I get it. In a world where the tools that users are given are often prescribed quite strictly for them, having one more tool to support is matter to be taken lightly: licensing costs, support, training, and all the normal fun IT things.

For these reasons, I prefer to think of EV as another tool in the toolbox for my users – not the exclusive tool. It’s not the end-all-be-all enterprise reporting solution like Financial Reporting, and it’s also a distinct experience apart from Smart View. Consider these tools:

  • Smart View
  • Planning
  • Financial Reporting
  • Tableau
  • Dodeca
  • cubus EV

Then consider the following evaluation criteria:

  • Ease of use / Learning curve
  • Report definition handled by user or admin?
  • Installation
  • Data visualization ability
  • Primary usage reason
  • Relation to other tools

I won’t exhaustively put these all on a spectrum for each property (I’ll save that for a future post), but looking at a few of these products and these evaluation criteria, I can point out a few things.

Smart View, Tableau, and EV are all ostensibly self-serve: you just make them available to the user, they point it at a data source, and then perform analysis and reporting, much to their bean counting merriment. Planning, Dodeca, and Financial Reporting ostensibly require some administrator to have put in some structure ahead of time that the user will consume.

As for ease of use, EV certainly isn’t harder to use than Smart View, if anything it’s a bit simpler. EV makes it hard if not impossible to put your grid into an inconsistent state with respect to the underlying OLAP data source, meaning that you can’t really screw things up by moving some member to the wrong column. Easier to use than EV, however, would be Dodeca and FR. Planning gets kind of its own special place on this spectrum (it’s not easy per se, it’s not hard… it’s something). Similarly for Tableau – a bit of a learning curve, simple reports are fairly straightforward, but the sky is the limit for some of the crazy visualizations you can do.

Speaking of data visualization, Tableau is quite clearly the champ out of all of these. Dodeca and Smart View have similar support for charting (by way of Excel or Excel-like charts). EV’s isn’t ostensibly a data visualization environment, but it’s visualization capabilities in terms of bread and butter charting are compelling, particularly the way that it is an absolutely seamless experience with respect to the data grid you’re working with. In other words, with Excel/Smart View you add in a chart if you want, in EV the data IS the chart if you want it to be. Or it’s just the data – or it’s both at the same time.

Installation for EV is pretty straightforward and a little better than Smart View since there isn’t an installer to worry about, so it’s nice being able to just give your use a URL and away they go. Similar props for Dodeca and most of the other tools on this list.

Final Thoughts

So what does this all add up to? I think that EV is a great tool to have IN the toolbox, but not the ONLY tool in the toolbox. Almost paradoxically it is a compelling tool for your advanced Smart View users but also for Smart View novices that may be intimidated by ad hoc queries and multi-dimensional concepts. EV rewards the user of a well-constructed cube, with a competent and functional UI that extends the value of properly deployed features, such as Dynamic Time Series, UDAs, attribute dimensions, sensible member names, and more.

On the other hand, it doesn’t seem to be for everyone: based on my own prior experience, it can be a confusing addition to the technological landscape for some IT managers (not to mention one more mouth to feed, system-wise), and might run into “But we already have X for that” syndrome. Again, I think it’s a complement and not a replacement or enterprise standard. There are countless scenarios I can imagine where if I were to be dropped into some enterprise as the benevolent dictator of all things BI (or OLAP, or EPM, or whatever), I would say “let’s take this thing out for a spin and see what people think” and would give Decision Systems or cubus a call.

Drillbridge Token Parameter Overview

Tokens in Drillbridge are a large part of the secret sauce that makes everything work so well. They are simple, flexible, and powerful. If you haven’t noticed, they are essentially JSON strings that have a range of allowable values. The most common of these are “name” and “expression”, but there are actually a handful of other keys that are less well known but incredibly useful. I’m going to cover the current state of Drillbridge tokens in this post.

As of the current version of Drillbridge (version 1.5.2), the following parameters on tokens are recognized:

  • name
  • expression
  • drillToBottom
  • sampleValue
  • quoteMembers
  • suppressParentheses
  • overflow
  • overflowAt
  • flags

Parameter: name

The name parameter has been around since day one. It identifies which dimension the token is replacing text for. For example, if the expression for the token will be generating the names of members in the Time dimension, then the value of name would be Time. The name here should exactly match the dimension, even including spaces. For example, if the dimension is named “Time Periods” then the value for this token should be “Time Periods” and NOT “Time_Periods”. This is a common issue that pops up (I’m guilty of it myself).

Parameter: expression

The expression parameter is where most of the magic happens. A lot has been written about expressions in Drillbridge on the Drillbridge Wiki so I will save some examples of expressions for a future article.

Parameter: drillToBottom

Possible values for this parameter are either “true” or “false” and nothing else. The default for this token value is false, so if you aren’t drilling to the bottom then you don’t even need to include this. This option is used to indicate that the incoming member should have its level-0 descendants pulled from the outline, then have transformation in the expression applied to them. For example, if you drill on Qtr1, the values that get processed by Drillbridge from the outline are Jan, Feb, and Mar, and if you have the #monthToDigit function as part of your expression, these will be converted to 1, 2, and 3. Note that when using this setting, you almost always need to have an associated Essbase cube on your report (so it knows which cube outline to search).

Parameter: sampleValue

Used purely to help testing. Putting a value here will simply pre-populate the text box on the report test screen so that you don’t have to keep putting values in by hand. It is really, really, handy.

Parameter: quoteMembers

By default, this is set to true. On queries where multiple values are pulled from the outline, you will typically use an IN SQL clause. For example, the generated SQL might be something like “WHERE Time IN (‘1’, ‘2’, ‘3’)”. This happens if Drillbridge generated the values of 1, 2, and 3. As you can see, each of these values has a single quote before and after it – Drillbridge put that in automatically. There are some cases where you won’t want Drillbridge to automatically put quotes in, such as advanced queries where you need to define the structure a little differently (such as for performance reasons). Possible values for this parameter are true or false. If false then the generated code in this example would be like this: “WHERE Time IN (1, 2, 3)”.

Parameter: suppressParentheses

Similar to the previous example, when Drillbridge generates a list of members, it will automatically enclose it within parentheses. For example, the parentheses surrounding 1/2/3 here: “WHERE Time IN (‘1’, ‘2’, ‘3’)”. Most SQL dialects seem to enclose their lists with parentheses, but for ones that don’t or where you need more control over the generated code, you can set suppressParentheses to true, and Drillbridge will not add in the parentheses for you. If you need to get creative with how you write your query (again, probably for performance reasons) then you might find yourself using this parameter.

Parameter: overflow

The overflow parameter is a relatively new addition. Some SQL languages and versions have a limit to the number of things you can put in the IN clause. For many versions of Oracle, this is 1,000. The normal way of programming around this (and what most versions of Drillbridge do) is just clip anything over 1,000, so that your query won’t fail. But what if you just absolutely need those 1,500 things or 10,000 or whatever it is? Modern Drillbridge versions have your back. The overflow parameter let’s you specify an additional template to append to the query, then replace it with additional members from the list of generated members.

Here’s an example: let’s say that there are an arbitrary number of accounts in the Account dimension, named Account 0000001 through Account 1000000. Let’s also say that drillToBottom is set to true and that the member that the user drills on would result in some 5,000 members being queried. Again, the normal behavior to handle this would be to clip the member list to 1,000 or whatever the default setting is (it can be configured in Drillbridge to whatever you want), then fire off the query. So the relevant portion of the query would look something like this:

WHERE Account IN ('Account 0000001', 'Account 0000002', ... , 'Account 0001000')

Using the overflow parameter, you could specify an overflow value of the following:

overflow: " OR Account IN %%OVERFLOW%%"

What Drillbridge will do is use the first 1,000 entries as it normally would, then cycle through the rest, generating subsequent lists that are placed into the %%OVERFLOW%% text inside the overflow token, then the whole thing is appended to the whole token replacement. This let’s us sidestep the natural 1,000 or whatever item limit on many RDBMS.

Parameter: overflowAt

This token expects a numerical value and it just specifies how big the groups should be that members are broken up into for the overflow parameter. The default is 1,000 but depending on your needs you may wish to set it lower or higher.

Parameters: flags

Flags is meant to be a sort of catchall bucket for various configuration settings that might be specific to the underlying technology without having to create official parameters. It is a comma-delimited list of flags. The only recognized flag at the moment is “~”, such that your flags parameter would look like this:

"flags": "~"

This is a special flag that comes into play on a Essbase Member Resolver. For tokens using drillToBottom, this tells the Essbase routine that fetch the descendants of the drilled member to ignore any members that have a consolidation operator of ~.

Thoughts on deprecated Essbase 11.1.2.4 features and the future of EAS

The Hyperion blogging-verse has been quite aflutter with the release of 11.1.2.4. So I won’t bore you with a recap on new features, since that has been done quite effectively by my esteemed colleagues. I will say, however, that by all accounts it seems to be a great release.

Oracle is really starting to hit their stride with EPM releases.

As a brief aside: I seem to be in the relative minority of EPM developers in that I come from the computer science side of things (as opposed to Finance), so believe me when I say there is a tremendous amount of energy and time spent to develop great software: writing code, testing, documenting, and more. Software is kind of this odd beast that gets more and more complex the more you add on to it.

Sometimes the best thing a developer can do is delete code and remove features, rather than add things on. This is a very natural thing to happen in software development. Removing features can result in cleaner sections of code that are faster and easier to reason about. It typically sets the stage for developing something better down the road.

In the software world there is this notion of “deprecating” features. This generally means the developer is saying “Hey, we’re leaving this feature in – for now – but we discourage you from building anything that relies on it, we don’t really support it, and there’s a good chance that we are going to remove it completely in a future release.”

With that in mind, it was with a curious eye that I read the Essbase 11.1.2.4 release notes the other day – not so much with regard to what was added, but to what was taken away (deprecated). EIS is still dead (no surprise), the Visual Basic API is a dead end (again, not a secret), some essbase.cfg settings are dead, direct I/O (I have yet to hear a success story with direct I/O…), zlib block compression is gone (I’m oddly sad about this), but interesting for now is this little tidbit: the Essbase Administration Services Java API is deprecated.

For those of you who aren’t aware, there is a normal Java API for Essbase that you may have heard of, but lurking off in the corner has been a Java API for EAS. This was a smallish API that one could use to create custom modules for EAS. It let you hook into the EAS GUI and add your own menu items and things. I played with it a little bit years ago and wrote a couple of small things for it, but nothing too fancy. As far as I know, the EAS Java API never really got used for anything major that wasn’t written by Oracle.

So, why deprecate this now? Like I said, it’s kind of Oracle’s way of saying to everyone, “Hey, don’t put resources into this, in fact, it’s going away and if you do put resources into it, and then you realize you wasted your time and money, we’re going to point to these release notes and say, hey, we told you so.”

Why is this interesting? A couple of things. One, I’m sad that I have to cross off a cool idea for a side project I had (because I’d rather not develop for something that’s being killed).

Two (and perhaps actually interesting), to me it signals that Oracle is reducing the “surface area” of EAS, as it were, so that they can more easily pivot to an EAS replacement. I’m not privy to any information from Oracle, but I see two possible roads to go down, both of which involve killing EAS:

Option 1: EAS gets reimplemented into a unified tool alongside Essbase Studio’s development environment.

Option 2: EAS functionality gets moved to the web with an ADF based front-end similar in nature to Planning’s web-based front-end.

I believe Option 2 the more likely play.

I always got the impression from the Essbase Studio development environment that it was meant to more or less absorb EAS functionality (at least, more than it actually ever did). I say this based on early screenshots I saw and my interpretation of its current functionality. Also, Essbase Studio is implemented on the same framework that Eclipse (one of the most popular Java programming environments) is, which is to say that it’s implemented on an incredibly rich, modular, flexible environment that looks good on multiple OS environments and is easy to update.

In terms of choosing a client-side/native framework to build tools on, this would be the obvious choice for Oracle to make (and again, it seems like they did make this choice some time ago, then pulled back from it).

The alternative to a rich “fat client” is to go to the web. The web is a much more capable place than it was back in the Application Manager and original EAS days. My goodness, look at the Hyperion Planning and FDMEE interfaces and all the other magic that gets written with ADF. Clearly, it’s absolutely technically possible to implement the functionality that EAS provides in a web-based paradigm. Not only is it possible, but it also fits in great with the cloud.

In other words, if you’re paying whatever per month for your PBCS subscription, and you get a web-based interface to manage everything, how much of a jump is it for you to put Essbase itself in the cloud, and also have a web interface for managing that? Not much of a leap at all.

cubus outperform EV Analytics Review: Using EV continued

Welcome to the third part of my review of cubus outperform EV Analytics. You might want to check out the first and second parts if you’re just coming in.

Last time I left off with using EV for grid oriented operations, but EV has some compelling chart features that I wanted to go over. Probably the most compelling aspect of EV’s charting functionality is that you essentially get it for free, and it is seamlessly integrated into the EV experience.

Recall that in the previous EV reviews I did, I mentioned that EV is less freeform than Excel, in a good way. By tightening the user interaction model, EV can provide a more polished user experience. This is true of charts as well. For any given grid you may be looking at, you just have to click the chart button and EV will generate a chart. Note that EV doesn’t generate an image object for you, it literally just changes your view of the data to be a chart rather than a grid.

Here’s an example:

A simple Sample/Basic view that has been changed into a chart

A simple Sample/Basic view that has been changed into a chart

The chart data is based on Sample/Basic as with before. The rows and columns have been used to generate the chart, and the POV/Page members are just “global” members as before. What’s really awesome is that you can click on the members (like Qtr1, Qtr2) and bring up the same member selection dialog as with before. Any member selection changes you make will be automatically reflected in real time, so there’s no chart to update or regenerate.

You can also choose to see both data and a chart at the same time, like this:

Chart and data shown at the same time (the 'Both' option)

Chart and data shown at the same time (the ‘Both’ option)

This is known as “Both” mode as opposed to Table or Chart which we have now seen. EV provides some rudimentary options for arranging the positions of things. In this case it’s horizontal with chart on the bottom but this can be easily configured a little differently. Other types of charts are also provided.

The charting capabilities in EV won’t give advanced visualization tools like Tableau a run for their money, but they are definitely on par with charting in Excel and in terms of the ease of use, they are much more approachable than Tableau and even Excel. I used to create decks of reports using EV and while they were generally table-based, there were a few “jawdropper” tabs where a particular bar or pie chart really hit a point home.

This article concludes my high-level overview of EV features. This has not been an exhaustive review of EV features, mind you, as EV has some other features that are interesting. These include Canvases (multiple EV views on a single page), user input (like lock and send or submitting data), dynamic selections, awesome support for attribute dimensions and dynamic time series, traffic  lighting data cells based on value, and more.

The next part and final part of this series will consider EV’s position within the enterprise and how it stacks up against other tools, including Smart View, Planning, Dodeca, and Tableau.

Drillbridge handling dimensions with spaces

Drillbridge’s powerful mapping abilities handle most common transformations with ease – stripping a prefix, converting a month into digits, or letting you use the Java language itself to parse a string however you need. Every dimension that gets drilled from is represented as a variable that a Drillbridge administrator can use to build their own expression. For example, if there is a dimension called Years in the cube, and the user drills on a member named FY15, then on the report side of things, there will be a variable referenced as #Years that will have the value of “FY15”. From here it would be typical to use the #removeStarting function to strip off the FY, and perhaps prepend the string “20”, then use that in a database query.

Something interesting happens, however, when the dimension has spaces in it. Let’s say that the dimension name is “Time Periods”. For perhaps obvious reasons, Drillbridge can’t create a variable with a space in it, because it’d look like a variable followed by a string (like #Time Periods). Instead, Drillbridge will replace spaces with underscores for purposes of creating a variable. In this case the variable would be #Time_Periods.

So #Time_Periods is the variable that is used inside the token expression, but what about the name of a token? The name of a token should still be the name of the dimension, using spaces, not underscores.

Consider the following tokenized query:

SELECT *
FROM Transactions
WHERE MONTH(PostDate) IN {{
"name" : "Time Periods",
"expression": "#monthAbbreviationToDigit(#Time_Periods)",
"drillToBottom": "true", "quoteMembers": "false"
}}

As you can see, the name parameter has the space, but the expression uses the underscored version of the variable name. In this case we’re also using the very handy #monthAbbreviationToDigit built-in function that can convert month names like Jan, Feb, and so on to 1, 2, or the proper month number.

Deleted journalized rows in ODI populating target with NULLs

This issue has been on my radar for awhile and I finally had a chance to dig into it the other day. I noticed an occasional issue for an ODI job that journalized a large source table and updated corresponding rows in a target on another system. The source and target are both Oracle databases so I’m using the DBLINK LKM and in this case using the MERGE IKM.

If you’re not familiar with journalized tables in ODI, it’s a really great feature that allows you to track rows that are inserted/updated on a table and then process only those rows when you run the interface. This capability is leveraged simply by way of a checkbox to tell the interface to only process the new rows (of course, setting up the journalization itself is a couple of extra steps but it’s also pretty straightforward). Journalization is incredibly useful in a variety of situations. The main one is that if the source is huge, you can’t or don’t want to process the entire table just to sync up with a target.

In addition to being able to track inserted and updated rows, with a simple option you can also synchronize deletions. This means that if a row gets deleted in the source, you have the option to delete the corresponding row (based on the primary key) in the target. I’d say about half of the interfaces I make with journalization have this option turned on and half have it turned off.

That all being said, I have a few interfaces at a client that have synchronize deletions turned on, but the sources very rarely get a row deleted. In fact, they almost never get a row deleted. But these interfaces have been failing now and then and the root cause turns out to be that some rows in the source were being manually deleted. The actual error as seen in ODI, however, is that an insert to the target table is failing on account of the ODI job trying to insert null values into columns that require non-null values. Curiously, these rows were not null in the source. As it turns out, ODI was getting null values because it was doing a left outer join with deleted records (generating records with just a primary key and all null fields).

But wait, shouldn’t these be deleted? Yes! I dug through the steps in the Operator, however, and there was no step that was deleting these rows from the flow table. So let’s take a look at the steps in the IKM:

ODI Oracle MERGE IKM steps

 

I see two critical steps with Order 140 and 150. Step 140 “Synchronize deletions from journal” is running and does exactly what we think it should do: delete records from the target that were deleted in the source. So far so good.

VMware Fusion

But based on analysis, this left some rows in the flow table (the temporary ODI staging table) that were just getting reinserted once again. But there’s a step for handling that!

VMware Fusion

We have the “remove deleted rows from flow table” that removes the deleted rows from the flow table, so that they therefore won’t be part of the join that creates the rows to go to the target. But why isn’t that step running? Well, it’s set to only execute if the FLOW_CONTROL option is set, as shown below:

VMware FusionSo, perhaps counterintuitively (for me, right now, at least…) the synchronize deletions is dependent on FLOW_CONTROL being turned on, and the issue was that Flow Control was turned off in this job. I don’t think this is a bug, but if caught me by surprised. I turned the option back on (the default is actually on but it was off for whatever reason), regenerated the scenario, and this issue is solved for now. At the moment I am of the thinking that this step shouldn’t be tied to Flow Control but I’m not going to question it for now. Hopefully this helps someone out down the road (but more likely I’ll Google it in a couple of years and help myself out, which curiously was the original motivation for this blog…)

cubus outperform EV Analytics Review: Using EV

Welcome to Part two of three in my EV review. Yesterday described EV at a very high level: it’s a front-end to Essbase. As I mentioned, think of it as a very polished version of Smart View with a few tricks up its sleeve. Today I want to dive in a little bit on some of the user interface elements of cubus EV to try and convey what I think are some its most compelling features.

To start off with, let’s take a look at the venerable Sample/Basic app. In the following screen we have a default view of the cube pulled up (more or less analogous to the initial retrieve in Smart View).

A simple view of Sample/Basic while on the Navigate ribbon

A simple view of Sample/Basic while on the Navigate ribbon

Note a few things about this user experience and the program. First of all, we are inside of Internet Explorer. EV is not a separate install for users – you pass out the URL to users and they run it themselves. This can save some serious deployment time. A typical view inside of EV will be a single grid with a single data source. In other words, the usage model is one where you have multiple data sources or multiple grids. I’m not saying this is a bad thing, mind you – I think it’s a good thing because you are trading away some of the freeform nature of Excel in exchange for a really smooth user experience.

Everything else in the screenshot should seem pretty intuitive: as with Excel you can have multiple tabs open (sheets in Excel), the POV members (in this case Product, Market, Actual) are available at the top of the sheet, and you have you normal rows and columns (Profit and the various Quarters, in this case). The various toolbar buttons available are fairly predictable in their utility.

While we’re on this screenshot, I want to mention something about the ad hoc experience that I really like. Each member in a row or column is clickable and provides various capabilities. There are two arrows on each member (up and down) and you can click or right click on them. Clicking on the down arrow on Year, for example, will replace it with the children (Qtr1, Qtr2, Qtr3, Qtr4). Right clicking on the the down arrow would drill to the children but also retain the parent. Drilling up works in a similar fashion. It seems like a small UI touch but as I have said a few times, EV is all about that smooth user experience and I think it really rewards the user that wants to be fast and efficient with their ad hoc data exploration.

Still on the subject of defining the POV, let’s say that you know or want to find the specific members you want to show. By clicking on the member itself, you can bring up a member selection box for that dimension. This is another one of the really strong points of EV because you can pick the member and no matter where in the spread it is (row, column, leftmost, topmost, etc.) it’ll automatically adjust everything for you. For example, if you are looking at Actual and Budget from the Scenario dimension and have four Quarters selected, no matter where your Time axis is, you can select any other members from the Time dimension and it’ll move everything around automatically. This is one of my pet peeves with the Member Selection box in Smart View or the old Excel add-in where the EV user experience is amazing.

Here’s the EV dialog for selecting the members you want from a dimension:

A member selector in the Measures dimension showing selection options and icons denoting Label Only members

A member selector in the Measures dimension showing selection options and icons denoting Label Only members

Again, note the rich UI we get: icons for Label Only members in the outline have a little label tag on them, there are convenient buttons to select all of the members that are visible, to expand the whole hierarchy, to take the lowest, toggle aliases, and more. The Find… dialog is very convenient for quickly hanging down particular members with search criteria.

Again, as an incredibly minor detail I want to mention another UI element I love: if you want to select or deselect several members, you don’t have to individually click them. The list dialog employs this kind of smart drag where if you start highlighting a member and drag down, it selects all the members you drag to, and similarly if you are deselecting. Again, minor feature, but great user experience.

The other tab on this dialog let’s you pick the order of the members:

Choosing the order for selected measures

Choosing the order for selected measures

It’s a simple and intuitive dialog but has some really nice features for moving things around just how you want them, or sorting some criteria (like alphabetically or outline order). Again, the UI respects our time and gives us a way to get to exactly what we want as quickly as possible.

Moving on, let’s talk about formatting up the grid. Most traditional formatting options are available: bold, italic, background/foreground colors, and more. You can add spacing between rows and borders. Bold text is probably my most used format, followed by spacing rows out by a half a row width, then applying a border and possibly some decimal formatting.

Formatting options for cells

Formatting options for cells

Probably the best aspect of formatting a grid in EV is the way in which it cascades the formatting across the whole grid. Smart View these days provides improved support for pivoting around formatting, but EV has done this for years.

Think of formatting as applying to members and not particular cells. Let’s say that I have 13 members in my time dimension: all 12 months and a single total for the year. I want the total (Time/Year) for the year to be bold. I go to the formatting dialog for Time and then select bold. Now all the members in the row are bold. If I pivot Time, say from column to rows, the formatting moves with it. Now let’s say that I have different locations on this grid too, and I want the total for all locations to be underlined (for some reason). I right-click, apply the formatting, and now the totals for location are underlined and of course the total for location and time is both bold and underlined. I can pivot around all I want and this formatting will be retained. Nicely enough, if I decide to clear out all of the formatting for one of the members, I don’t have to go and “fix up” a single cell.

EV formatting is really convenient I wish Smart View was a little more like it, but I think that’s just how it has to be given the different environments and paradigms these programs use.

So let’s see here, so far we can choose a connection, pivot around, quickly drill down to the members we want to see, easily refine our member selection, reorder it, and apply formatting. Additionally, the formatting moves around easily, and is applied in a cascading manner so we don’t have to mess around with single cells and formatting getting whacky on us.

Check back tomorrow for a more of the EV user experience where I’ll cover basic chart capabilities and a few other goodies!