Enabling the Unknown Member on a Dimension in SSAS.

Do you ever get this error:

  • Warning 5 Errors in the OLAP storage engine: The attribute key cannot be found when processing: Table: ”, Column: ”, Value: ’12:00:00 AM’. The attribute is ‘Date’.

It turns out that this error occurs because:

  • Null values in the fact table relationship are converted to ’12:00 A.M.’

So I tried enabling the Unknown Member attribute of the Date table.  (It’s a standard Date table generated by the SSAS wizard for such things.)

No luck.  Still getting the error when the date in the Fact table is null.

It turns out that enabling the Unknown Member is a multi-step process.   (Much thanks to wildh for their post here.)

To enable the Unknown Member on a Dimension follow these steps:

  1. Go to the Properties Page of the Date Dimension you are using, and select UnknownMember visible AND set the UnknownMemberName to something, such as ‘Unknown’:
    Enable the Unknown Member in the Dimension
    Enable the Unknown Member in the Dimension
  2. Now, go to the Cube Definition and click on the Dimension Usage tab:

    Dimension Usage Tab with Date dimension Usage highlighted.
    Dimension Usage Tab with Date dimension Usage highlighted.
  3. Next, click on the button next to the Date dimension usage, and then click the advanced button in the bottom right:

    Select the Advanced button
    Select the Advanced button
  4. Finally, change the relationship Null Processing drop down to Unknown Member:

    Select the UnkownMember option in the Null Processing dropdown
    Select the UnkownMember option in the Null Processing dropdown
  5. Click OK. To get back to the Dimension Usage tab, and change the Null Processing dropdown for any other Date relationship where the date in the Fact table could be null.

BI Data Source – SSAS

Well now.  It turns out that PerformancePoint for SharePoint has quite a few nifty little features (after reading the help topics on the blank PerformancePoint site), but to work with it properly I need to have some data in a SQL Server Analysis Services (SSAS) Cube.  Since we are still in the stone ages and running on SQL Server 2008 R2 (hey, at least we moved to R2 last year), I will need to get some reasonably slice-and-diceable data in there at first.  I have an excellent sample dataset comprising the last 2 years of Head Count data (i.e. number of people who work here).  This can be sliced by State, District, Top level Manager, HQ or Not, and Location.

So, before I can start dashboarding, I have to do some ETL to get our sooooo not-in-star-schema raw data cleaned and into the Cube.  Since our data lives in Oracle, I will  shunt it to a SQL Server database first.  For this, I will use the Attunity drivers.  If you haven’t heard of these, and you work with SSIS and Oracle (or Teradata), you should look into them.  They’re from Microsoft, they’re free and they work in 2008 and 2012.  They are 10-50 times faster than Oracle’s drivers and Microsoft’s Oracle drivers.

Meanwhile, I installed the Dundas Dashboard software on my local machine so that I could check it out.  The install went fine, it created a SQL Server instance for itself and setup the tools as expected.  There are a ton of additional features (most of which I installed) and add-ons (none of which I installed – yet).

I also installed the free MicroStrategy Analytics Desktop.  Turns out this is a web app so it installed locally on port 8082.  Interesting.

So, once I’ve moved the Headcount Data into the Cube, I can try out the Dashboarding in PerformancePoint, Dundas and MicroStrategy to see what’s what.