Welcome to EMC Consulting Blogs Sign in | Join | Help

David Francis Blog

  • PerformancePoint Server 2007 SP1 - Possible release date

    Just a quick note to say that SP1 for PerformancePoint is likely to be available early May. I don't have any specific contents for it yet.
  • PerformancePoint Time Intelligence - BI for the masses Part 1

    Microsoft have often mentioned their desire in the past for 'BI for Masses', giving the power of business intelligence to everyone who might need data to make a decision rather than just a few power analysts. For most users and lets face it lots of analysts too, their exposure to BI actually comes in the form of data presented to them in Excel and it is Excel style formulas that they are most comfortable with.

    I think this was probably in mind when Microsoft designed their syntax for creating and using Time Intelligence filters within Microsoft PerformancePoint Dashboard Designer.

    Before I go on, its probably worth a quick note on Time Intelligence Filters and what they are used for.

    Time Intelligence filters within PerformancePoint provide users with a way of specifying dates using everyday terms like Today, Yesterday, Last 6 months etc rather than having to select from a calendar control or pick different measures/metrics.

    The Time Intelligence filter can then be linked to your dashboard content to drive your scorecards, grids and charts etc (more on that later).

    So how to do you create one?

    • Start up PerformancePoint Dashboard Designer and create a new OLAP data source (if you don't have one already).
    • Go to the Time tab and map your Time dimension to the Time Intelligence level settings.

    The clever bit here is you are mapping PerformancePoint's Time Intelligence to the Time dimension that you have created and it will now automatically keep track of the current day for you.

    No more Time dimension default member formulas or trying to use VBA and Date now.

    Of course the designer still has to have an appreciation of dimensions, hierarchies and levels.

    • Now create a new Dashboard.
    • Click on the filters tab of your new Dashboard and click on New Filter.
    • Select Time Intelligence (the one on the left) from the Dashboard Filter Template.

    • Name your new filter.

    • Select the data source that you used to define the Time mappings.

    • Now start to create your Time Intelligence formulas and add your own user friendly names.

    • Notice the lack of MDX (its still there, just click preview) and the use of Time Intelligence expressions which whilst still having a MDX flavour are also Excel-like.

    • So some examples of Time Intelligence Formulas and Function (courtesy of Microsoft).

    The following table shows examples of TI syntax expressions.

    TI expression formatExample

    » Specify current period with an offset
       Member + Integer
       Member – Integer


    » Specify a range of continuous time periods
       MemberExpression1: MemberExpression2


    » Specify a parallel period with a specific lag
       (MemberExpression1-Integer). MemberExpression2


    » Specify a year- or month-to-date period


    » Specify time hierarchy levels with a function


    » Specify a list of time periods


    You can combine different syntax forms. For example, this expression is a list of several members:


    Notice that one of the member expressions in this list is a range expression, Month-5:Month-2. This range expression actually specifies four individual members: Month-5, Month-4, Month-3, and Month-2.

    What functions can I use with TI Members?

    TI syntax expressions can also include a member function, in the form Member.MemberFunction. This table shows the TI member functions.

    Parent The parent level of time aggregation from the data source Day.Parent
    FirstChildThe first child aggregation level from the data sourceMonth.FirstChild
    LastChildThe last child aggregation level from the data source Month.LastChild
    FirstSemesterReturns the first semester of the parent time period. The parent member for this function must be Year. Year.FirstSemester
    LastSemester Returns the last semester of the parent time period. The parent member for this function must be Year. Year.LastSemester
    FirstQuarterReturns the first quarter of the parent time period. The parent member for this function must be one of semester or year. Year.FirstQuarter Semester.FirstQuarter
    LastQuarterReturns the last quarter of the parent time period. The parent member for this function must be one of semester or year. Year.LastQuarter (Semester-1).LastQuarter
    FirstMonthReturns the first month of the parent period. Year.FirstMonth (Year-1).FirstMonth
    LastMonthReturns the last month of the parent period. Year.LastMonth (Quarter+1).LastMonth
    FirstWeekReturns the first full week of the parent period. Year.FirstWeek (Semester-1).FirstWeek
    LastWeekReturns the last full week of the parent period. Year.LastWeek (Semester+1).LastWeek
    FirstDayReturns the first day of the parent period. Month.FirstDay (Month-1).FirstDay
    LastDayReturns the last day of the parent period.Month.LastDay (Month+1).LastDay
    FirstHour Returns the first hour of the parent period. Day.FirstHour (Day-1).FirstHour
    LastHourReturns the last hour of the parent period.Day.LastHour (Day+1).LastHour
    FirstMinuteReturns the first minute of the parent period.Day.FirstMinute (Hour-2).FirstMinute
    LastMinuteReturns the last minute of the parent period. Day.LastMinute (Hour+2).LastMinute
    FirstSecondReturns the first second of the parent period.Hour.FirstSecond (Minute-10).FirstSecond
    LastSecondReturns the last second of the parent period. Hour.LastSecond (Minute+9).LastSecond

    • Then within your filter select a Display Method (I think a simple list works best here) and go onto to finish creating the filter.

    So, now how do you wire your new filter into your dashboard? Well, it's much the same process as that for the normal member selection filter.

    • Drag your new filter into either the header area or a zone that you have created for it to live in.

    • Now click on your new filter and select the name of your Analysis Services Cube Database (in the picture mine is just called Cube - you wouldn't call it that in real life!) and drag it into the 'Drop fields to create links area of your scorecard, grid, chart etc.

    • Choose whether you wish to link to filters, rows or columns if connecting to a scorecard or link to your Time Dimension if using an Analytic Grid or Chart.

    Hope you find Time Intelligence filters as useful as I have and a great way to improve usability and understanding for your front-end users.

    Technorati Profile

  • SSAS 2005 - Deployment issues following changes to statically linked measure groups

    If you are making changes to a linked object measure group after you have created the initial link, such as adding a new measure, you can come across the following error when you next deploy the solution.

    "Errors in the OLAP storage engine: The metadata for the statically linked measure group, with the name of XXX, cannot be verified against the source object."

    When I first came across this I expected just changing the Deployment Mode to Deploy All would fix it but this doesn't appear to be the case.

    Then I thought restarting the AS service would flush and reload the memory held metadata - no effect.

    The only way round this is:

    1) Backup your Analysis Services database.

    2) Delete the database.

    3) Redeploy the underlying linked cube and reprocess.

    4) Delete and recreate the link between the two objects.

    5) Redeploy and reprocess the rest of the Analysis Services database.

     Hope that helps.



  • SSAS 2005 - No mapping between account names and security IDs was done error on deployment

    On the off chance you haven't come across this one before:

    If you get the following error 'No mapping between account names and security IDs was done' when trying to deploy a cube project then check the membership of all the roles on your cube.

    Chances are one of the users listed has actually been removed from the Domain.

    Simply identify the 'offender', remove them from the role, check your altered role back into source control and redeploy.

  • PerformancePoint CTP4 now available

    PerformancePoint CTP4 is now available as of yesterday on the Connect download site.

    Courtesy of Microsoft, here are the new feature lists.

    PerformancePoint Planning

    Business Modeler

    ·         Business Rule Templates.  Several additional rule templates have been added.

    ·         PPSCmd.  The command-line tool had some additions to provide ability for scripting of overall system processes.  For example, the ability to deploy and reprocess models and data.

    ·         Migration.  Additional feature work was done to enable migration of an application between two server environments.

    ·         Jobs.  Improved user experience for executing and managing jobs.

    ·         Data Types.  Dimension member properties now support more data types.

    Excel Client

    ·         Offline Cache.  Improved management of locally cached objects.

    ·         Templates.  Additional report templates have been added.

    ·         Review and Approval.  Reviewers and Approvers can now manage submissions through a single dialog.  Support has been added for bulk operations.

    ·         Filters.  Form and report filters now support dimension properties.


    ·         Performance. Several changes were made to improve overall server performance.  Additionally, Financial Job and calculations have specific performance enhancements.

    ·         System Information.  Error message reporting and system logging has been improved from prior releases.

    ·         Associations.  Improved capabilities for working with complex associations.  Better performance handling large associations and movement of large data set.

    ·         Security.  Public interfaces have improved resistance to any potentially malicious attacks.

    PerformancePoint Monitoring


    ·         OLAP View Sorting/Filtering.  Users can now sort (by column) and filter empty rows/columns in Analytic Charts and Grids.

    ·         OLAP View Types.  Users can now switch between grids and charts and also change chart types (includes bar charts, stacked bar charts, stacked 100% bar charts, line charts and combined bar/line charts).

    ·         OLAP Member Properties in Grid.  Users can now add attributes of a member into the OLAP grid.

    ·         Cell Level Actions.  Users can now see the cell level actions behind a value in an OLAP grid or chart.

    ·         Export to PowerPoint.  SharePoint users can now export dashboard views to Microsoft Office PowerPoint.

    ·         Multiple Filters.  Users can now pass multiple dashboard filters to scorecards and report views.

    ·         Dashboard Viewer for SharePoint Services  Users can now add PerformancePoint Monitoring dashboard items to an existing SharePoint page through a new PerformancePoint Web Part.

    Dashboard Designer

    ·         Dashboard Designer Ribbon Changes.  Usability improvements within the Dashboard Designer Ribbon, which is part of the Microsoft® Office Fluent™ User Interface.

    ·         OLAP View Configuration.  Enables light configuration options on charts and grid (fonts, formats, chart legend placement, grid layout).

    ·         PAS Integration.  Users can now add PAS report views into their dashboards.

    ·         Time Intelligence KPI Filters.  Users can now add Time Intelligence expressions to individual KPIs.

    ·         Scorecard Filters.  Users can now  pass members from scorecards into report views. (Example:  Passing the KPI Name to an SSRS report.)

    Excel Services Support

    ·         Excel Services as a Data Source. Users can now import tables or named ranges from Excel Services spreadsheets.

    For me its great to see a larger of variety of chart types, the ability to sort view and filter empty rows and also the chance for a little light formatting, all bugbears on my current project and things we've been asking for. Thanks.

  • SSAS Analysis Services MDX YTD function doesn't work with a Fiscal Year attribute type

    I suspect that many of you have already come across this in your day to day MDX'ing but just in case:

    The standard YTD function only works with the Year level of Time hierarchies when the Attribute Type is set in the Basic Properties section to type Years.

    If you have a Fiscal Hierarchy in your Time dimension and have the Type set to FiscalYears you will get #Value when you browse any measure using the YTD function.

    If for any reason your application requires that Type FiscalYears is used then use PeriodsToDate instead of YTD as it is not affected by the Attribute Type.

    Hope that helps.

  • PerformancePoint CTP4 - Due in early to mid August 2007

    Whoops, forgot to mention the date for CTP4 which is primarily a Planning functionality release but will have some changes to Monitoring and Analytics as well, see previous blog http://blogs.conchango.com/davidfrancis/archive/2007/07/21/PerformancePoint-CTP4-Monitoring-and-Analytics-_2D00_-Some-more-Proclarity-like-features-promised.aspx
  • PerformancePoint RTM and GA dates

    Alyson Powell Erwin at Microsoft has confirmed that the target RTM month for PerformancePoint is September 2007 with the General Availability release being several weeks later. Big Smile
  • PerformancePoint CTP4 Monitoring and Analytics - Some more Proclarity like features promised

    Just a quick note to mention that the following features have been promised for CTP4 M&A.

    • 'Sticky bit' filter selections - i.e. as you navigate across multiple pages, the filter selections from your previously viewed page will propagate to the next page you view preventing the user from having to choose them again.
      • This does depend on you having the same definition of that filter across all pages that you wish to use it. Sounds obvious but I've seen people using Proclarity dashboard create two different product filters, one for each page and then wonder the selection on page 1 doesn't transfer to page 2.
    • Non Empty rows and columns filter support for Analytic Charts and Grids.
      • This one has been giving me a headache with CTP3 on a client site. Currently in CTP3 you can set up your Analytic Chart and Grid view using a drag and drop designer (which is nice) but if you then go into the query part of the designer and add either NonEmpty or Non Empty MDX statements whilst the initial data set brought back does hide the rows, as soon as you revert back to the design view (which you have to) the MDX changes are lost. Grrrrrrr Angry
    • Ability to export from your dashboard to MS Powerpoint after your have deployed it to Sharepoint.
    • Ability to only publish certain web parts of your dashboard to a Sharepoint site, i.e. You only want to deploy the scorecard element of your overall dashboard to an existing Sharepoint page.


  • PerformancePoint CTP3 now available + PerformancePoint Server Management Reporter

    Due to being on-site with only a hot-spot connection, I haven't downloaded anything yet, but CTP3 is now available on Microsoft Connect. The planning functionality has remained the same as that in CTP2 with some hotfixes to follow but the Monitoring and Analytics has been improved.

    Some of the key ones are:

    • Multipage dashboards.
    • Addition of time intelligence filters - Based on current calendar date and allows you to have filters such as Last 2 Months, Last 3 quarters etc so more user friendly and hopefully more how users think.
    • Analytic grid and chart navigation - Right click giving drill down, drill up, expand, collapse, show only, remove, pivot and drill down to (cross drill to another hierarchy).
    • Drill to detail (+ export to Excel) and actions on Analytic Grid cells.
    • Ability to open a dashboard view in its own window, to reset a view back to its original published state and of course to export the view, if applicable, to Excel.
    • Excel Services integration with Excel Services Reports becoming an available dashboard view allowing to take advantage of some of the funky formatting you can now do in Excel 2007.
    • Data paging on large grids of data to reduce the amount shown at any one time in a view or can select show all instead.
    • KPI designer - can now set actuals and targets within the initial wizard.
    • Scorecard designer - improvements to member selector (can now right-click and select children, but you do appear to have to expand the node before you can select them)
    • Proclarity-like Analytic grid and chart builder - CTP2 you had to write the MDX, in CTP3 it is drag and drop items onto columns, rows and background. Also member selector for dimensions in this builder has select leaves, select children and select descendants at levels options.
    • Scorecard - Can attach different report views to a KPI including URL link to Proclarity views stored on a Proclarity Analytics Server (PAS). V1 of PerformancePoint hopes to be able to browse and view select directly from a PAS server rather than having to type in the URL.

    No new chart types other than Bar and Line so it looks like decomposition tree and heatmaps from Proclarity may stay in Proclarity for the time being with the current option of URL links to a PAS server.

    In addition, Microsoft have announced PerformancePoint Server Management Reporter.

    MSFT says 'Management Reporter is a reporting application designed to enable information workers to take control of their business by creating and analyzing feature rich reports.  It is an application tailored to general users for creating, maintaining, deploying and viewing boardroom quality financial statements.  Users can easily monitor the health of the business and quickly understand what’s happening at any point in time.  Management Reporter integrates with the General Ledger  (GL) system or Financial Data Mart (FDM) allowing users to run financial statements within minutes after installing the application.'

    Initial quick inspection suggests a report wizard driven whitespace financial reporting tool primarily aimed at working with chart of accounts, example of a report type in the wizard is Income Statement - Current and YTD.

Powered by Community Server (Personal Edition), by Telligent Systems