As one of the tasks on hand, I had to figure out the backlog which by definition is a running total of all ‘open’ incidents at the end of a day. The dimension table being used has all the incident details and related attributes such as status, priority, category, etc 

So, I created a caculated measure, named [Incident Backlog Count], based on the [Incident Count] measure in the measure group, as: 

IIF ([date hierarchy].CurrentMember IS NULL,
 IIF(IsEmpty(Measures.[Incident Count]),
  IIF([date hierarchy].Parent.FirstChild IS [date hierarchy].CurrentMember,
   (Measures.[Incident Backlog Count], [date hierarchy].Parent.PrevMember),
   (Measures.[Incident Backlog Count], [date hierarchy].PrevMember)),
  IIF([date hierarchy].LEVEL IS [date hierarchy].[Date],
   (Measures.[Incident Count]),  IIF(IsEmpty((Measures.[Incident Backlog Count],[Date Hierarchy].LastChild)),
   (Measures.[Incident Backlog Count],[Date Hierarchy].LastChild.PrevMember),
   (Measures.[Incident Backlog Count],[Date Hierarchy].LastChild))

The recursion worked well and the MDX seemed to simulate the LastNonEmptyChild function, except that at the leaf level, all the values were getting populated – even for the empty members !   

For the life of me, I could not figure out the reason why this was happening, until I went in the Calculated Member Form Editor for the calculated measure and set the Additional Property Non Empty Behavior to the default measure [Incident Count] 

Here’s what Microsoft has to say about the Calculated Member Form Editor 

If the Non Empty Behavior property is blank, the calculated member must be evaluated repeatedly to determine if a member is empty. If the Non Empty Behavior property contains the name of a measure, the calculated member is treated as empty if the specified measure is empty. 




There are 2 columns in my fact table, order_date and ship_date. Each row has an order_date, but ship_date is NULL, possibly for orders that are yet to be shipped, or were cancelled and never shipped. Pulling it in a cube kept generating the error message
Errors in the OLAP storage engine: The attribute key cannot be found when processing: Table: ‘table_name’, Column: ‘Column_name’, Value: ‘0’. The attribute is ‘attribute_date’.

This was happening when trying to lookup the key values for NULL/missing ship_date against time dimension

To resolve this, we need to let the Fact table ignore NULLs or zero’s in the data

In BIDS, open the Cube Structure Tab
In the Measurements pane, select the Fact table the error is related to
Change ErrorConfiguration property to (custom)

Expand the ErrorConfiguration property,
 set KetNotFound property to IgnoreError
 set NullKeyNotAllowed to IgnoreError

This will pass-through all NULL or 0 key values to the dimension as “Unknown Member”, leaving all error handling for referential integrity issues for the Dimension to handle.

Open the related Dimension
In the Dimension Structure pane, select the Dimension name (at root level)
Change UnknownMember to Hidden

Process the Dimension, and then process the Cube

I’ve been working with the other components of BI (SSIS, SSAS, SSRS, PPS) for the past 6 weeks and haven’t had much chance to work on the MOSS component.

Here is something I had to struggle with and hope you guys find it useful:
In the cube, one of the most common tasks is to do data analysis/comparison for different time periods and almost all the time, the reference point is NOW.
How did our numbers compare to a period in the past w.r.t. today/this month/this quarter, etc

Here are some MDX examples to solve 90% of those worries. Might come in handy if you want to create a named set based on such:

1. Current Month MDX
strtomember (“[Date Hierarchy].[Month].&[” + format(now(), “yyyyMM”) + “]”))

2. Current Quarter MDX
The VBA function format() does not have a provision for returning the current quarter, so here is what you do – as above, you get to the current month and lookup its parent to get to the quarter. Now, the caveat is that your date hierarchy should be defined as such (Month to be a child of Quarter). In my case, it is Year -> Quarter -> Month -> Date
strtomember (“[Date Hierarchy].[Month].&[” + format(now(), “yyyyMM”) + “]”).Parent)

It saved my day and hope it helps you.
In case you know of another (better or worse) way, let me know.

Now that we are learning more about the upcoming capabilities of Sharepoint 2010, its time to focus on relevant details. And for me, that includes the Business Intelligence capabilities being addressed in the release.

On Business Intelligence, from the Microsoft Sharepoint Team Blog:

Historically, business intelligence has been a specialized toolset used by a small set of users with little ad-hoc interactivity. Our approach is to unlock data and enable collaboration on the analysis to help everyone in the organization get richer insights. Excel Services is one of the popular features of SharePoint 2007 as people like the ease of creating models in Excel and publishing them to server for broad access while maintaining central control and one version of the truth. We are expanding on this SharePoint 2010 with new visualization, navigation and BI features. The top five investment areas:

1. Excel ServicesExcel rendering and interactivity in SharePoint gets better with richer pivoting, slicing and visualizations like heatmaps and sparklines. New REST support makes it easier to add server-based calculations and charts to web pages and mash-ups.

2. Performance Point Services – We enhanced scorecards, dashboard, key performance indicator and navigation features such as decomposition trees in SharePoint Server 2010 for the most sophisticated BI portals.

3. SQL Server – The SharePoint and SQL Server teams have worked together so SQL Server capabilities like Analysis Services and Reporting Services are easier to access from within SharePoint and Excel. We are exposing these interfaces and working with other BI vendors so they can plug in their solutions as well.

4. “Gemini” – “Gemini” is the name for a powerful new in memory database technology that lets Excel and Excel Services users navigate massive amounts of information without having to create or edit an OLAP cube. Imagine an Excel spreadsheet rendered (in the client or browser) with 100 million rows and you get the idea. Today at the SharePoint Conference, we announced the official name for “Gemini” is SQL Server PowerPivot for Excel and SharePoint.

5. Visio Services – As with Excel, users love the flexibility of creating rich diagrams in Visio. In 2010, we have added web rendering with interactivity and data binding including mashups from SharePoint with support for rendering Visio diagrams in a browser. We also added SharePoint workflow design support in Visio.

Here is a flash (or should I say Silverlight – ahem !) update for people following the hoopla around the next big thing from Microsoft around the corner – Office 2010 (and it is pronounced Twenty-Ten, not Two-Zero-One-Zero, or Two-Thousand-Ten, for the uninitiated):

Microsoft officially announced the name of its code Project Gemini, a BI revolutionary product integrated with Excel 2010, as PowerPivot

Details at Official PowerPivot site

Had my users complain about how painfully slow it is the first time they hit the portal every morning, and then things get back to normal. I had put it on the back-burner, but now that things are under control, I got some time to revisit this issue.

Every night, the App Pool recycles, and then pages are compiled from the generic MSIL to native code upon first use. This is known as just-in-time (JIT) compilation. If not performed beforehand, it can cause pages to load slower the first time they are requested.

This can be simulated by doing an iisreset on your server and benchmarking the time it takes to load a page… just for kicks

What I needed was a warmup script that would hit all critical webpages for me every night, after the recycle. Found this file that I customized to my use.

All credit goes to Joel Olson, and Andrew Connell

After playing around with my MOSS installation for some time, I realized that a lot of stuff resides in what Microsoft euphemistically calls the ’12 hive’. So, instead of pulling up the command prompt everytime and navigating to the directory, I decided to create a desktop shortcut.

  1. Go to the Desktop on the server that is running SharePoint Server 2007
  2. Right click and choose New -> Shortcut
  3. Type %windir%\system32\cmd.exe in the location textbox, click Next
  4. Name the shortcut as Shortcut to 12 Hive or anything else you want, click Finish
  5. You will see the icon for the shortcut you just created on the Desktop
  6. Right click the icon and choose Properties
  7. On the Shortcut tab, change the value in the Start in textbox to the location of the 12 hive,
    type “%PROGRAMFILES%\Common Files\Microsoft Shared\web server extensions\12\bin”
  8. Click OK.

Of course, the other way would be to have it in your PATH

Now now, not everybody has the latest version of Excel on their desktops. Does the mean they are SOL ? Not quite, there is a way you can use Excel 2003 as a client to ‘publish’ spreadsheets for use with Excel services.

Make sure you have the Office 2007 Compatibility Pack installed. You need this to be able to save your Excel file in Excel 2007 format.

I had promised to bring the features and review the same for the neat Sharepoint Deployment Tool on Codeplex

Ability to Pick and Choose content

Say you have finished developing and unit testing the code, and now it needs to be pushed into UAT. You might not want to export/import the whole site, as there might be pieces you are still working on. This tool comes in handy in such cases as you have the ability to pick and choose the items you want to deploy, down to the individual files, items, lists level. This is very useful when deploying/migrating content from one environment to the next.

Dependant objects can be selected

Any referenced objects, master pages, CSS files, images, etc are included by default as a part of the export and that greatly reduces the chances of something ‘breaking’ as a part of the deployment. You have the option of choosing ‘Exclude dependencies of selected objects’, of you so desire.

Retain Object GUID

If you have used stsadm, you might have noticed that there is no option to retain GUIDs when deploying sites. This makes troubleshooting and debugging a lot more difficult. This tool retains object GUIDs as a part of deployment. This helps in incremental deployment on sites that already exist on the target.

Versioning Flexibility

You have the choice of getting the latest major version, major and minor version, or all versions of any and all items you are looking to deploy. This helps make incremental changes possible for objects that need to go in on top of what already exists on the target.

And best of all, its FREE


Nothing is perfect, of course.

Some shortcomings

Does not work for the following types of content – recycle-bin, alerts, audit trail, change log history, workflow tasks/state.

Does not work for system level files – features, assemblies, solutions, etc

Cannot transfer list items to another at a different URL. Click here for a discussion on this.

Overall, even with the few limitations, it is a great tool and something you should check out and put in your toolbox. It will definitely come in handy.

I have been struggling with the best way to migrate and deploy content between farms from the development to testing environment, finally to production. The stsadmin options work best for a first-time deployment, but as things change and more code is to be pushed out on an incremental basis, those options fall short.

Looking at codeplex, I came across this tool. Will post a detailed review later

Sharepoint Deployment Tool on Codeplex

According to the author,  Chris O’Brien

The SharePoint Content Deployment Wizard is a tool for SharePoint 2007 which provides the means to deploy the following content:
– site collections
– webs
– lists
– folders
– list items (including files)

Content is exported using the Content Migration API (PRIME) as a .cmp file (Content Migration Package) which can be copied to other servers for import. Unlike the out-of-the-box tools, the Wizard allows *granular* selection of content via a treeview.


July 2018
« Mar    

Blog Stats

  • 44,396 hits