Sacha Tomey's blog

Recently Sacha published links to some of the nuggets that were posted here from the training course I went on a few weeks ago.  I got a lot of traffic from those links so thanks!

Some of the nuggets were straight out of my shorthand notes, which, looking at them now, may not make a lot of sense.

Will try to clarify some of the ones he pointed out to me.  His comments in bold, mine in italics.

                • Remove blank columns and remove blank rows in the Report Properties - Matrix properties

Apparently this improves performance.  It is a setting in the Excel Add-in under Report Properties.

                • Create a separate section with "Suggested changes" with read/write access or use Sharepoint Library.  Create Groups for Detail & Summary reports.

This was a suggestion to add a spreadsheet with “Suggested changes” for users to add their feedback.  Another suggestion was to use the Grouping features in Excel to create Summary/Detail sections with expand/collapse capabilities.  Users like these simulated drilldown capabilities.

                • Select False to validation will ignore unknown member data.

This was in reference to a parameter used with the Label stored procedures for staging and validatating dimension members.  The parameters are mutually exclusive, that is you need to run the procedure twice with the different parameters to validate annotations & validate members. 

                • Always set cycle dates to at least 1 day after the current date, then manually instantiate the cycle afterwards.  This solves issue with waiting for cycle to appear automatically.

You can manually instantiate a cycle even if you leave it to start on the current date.  Not sure of the benefit of adding a day.  You can either see if the cycle starts straight away or manually instantiate it – during the build phase I tend to reduce the polling time to around 10 seconds so you hardly ever have to wait.  Just need to remember to reset it once live..

Setting the cycle date to 1 day after the current date mitigates the problem where sometimes cycles do not get instantiated.  Was a suggestion from one of the attendees – reducing polling time is another good method.

                • Create referential integrity within dimension properties by assigning references to other dimensions.  Adding new properties will check data within the other dimension.

This was in reference to setting up dimension properties.  Dimension properties can be pulled from other dimensions (or at least it is my understanding that a reference can be made to those.)  In the dimension modeler, there is an automatic referential integrity check if this link is in place.

                • All users have to have read access to all Account members.  You have to design report without salary line or use separate models when dealing with separate information like salary.  You can also create an alternate member set without Salary, and then Create a member set with Salary.

All users have to have read access to all account members - is that a statement?  I'm not sure I agree.

I am not sure about this one either.

                • Use fake data for IFRS - SOX requirements - when dealing with Salary information.  Modeler should not have access to data.

                • Create a property on Account dimension - Restricted.  Assign restricted accounts.  In Report Properties for Filters, Setup Filter to only show unrestricted accounts in the filter.  Set True Hide & True Lock on filter.

There were some comments during the sessions about protecting restricted account data from developers.  There is still not a good way of doing this in my opinion as data has to be seeded at the leaf level and available for rollups.

 

Sacha Tomey's blog