Stumbling on 'Report Server has encountered a Sharepoint error'

Scenario: A Sharepoint 2007 or 2010 environment. You copy or move a Reporting Services report - based on a shared data source found on a dedicated Data Sources library -
from one library to another or even to another folder within the same library.

Result: Opening the report fails with the error:
Report Server has encountered a Sharepoint error ( rsSharepointError ).
The same error if you look at the Data Sources or Manage Parameters: Report Server has encountered a Sharepoint error.

Changing the report name and refreshing the page will not help.
But here is a way that may solve your problem :

1) Select Open in Report Builder. You may get the error :
"The Report Definition was retrieved from the server but one or more errors occurred while retrieving report properties".
If you click on Details button a new generical error message pops up:
"An error occurred when retrieving report parameters for report ....." . Click on OK and ignore the message so far.

2) Run the report in Report Builder. You may set the credentials to report's data sources in order to make it work.

3) Save the report to your sharepoint library. (The library where you opened the report in step 1). But the item still exists and if you accept replacing it than it will fail with the same error message : "Report Server has encountered a Sharepoint error".  

4) Delete the report item from the list then save the report.

5) Select the report item from the sharepoint library. Select your report Data Sources. If saving the report caused a custom data source then replace it with the original shared Data Source. Launching the report will succeed.

But looking back at all those steps, it may make sense not to copy or move the report. Open it in Report Builder and save it to the target sharepoint library.       

But one question still remains : Why all those steps for a basic copy or move task ?

Data and a box of matches

The way we think IT is both inspired and explained from our normal daily life.

You may perhaps still remember that inheritance in OOP is introduced in most of the books by an association with 'genetic' inheritance in the animal world, polymorphism with transportation by vehicles operated differently, etc.

A common pattern you often encounter is to retrieve some data. The retrieved data is used for ongoing lookup tasks, normally based on an input key.

At a conceptual level there are perhaps two ways to accomplish it :

1. Access the underlying data each time the lookup operation is needed: directly or in a SOA like manner by calling the service layer on top of your data.
2. Retrieve and cache all your lookup data in a hashtable data structure. If you are dealing with 'slow changing' data, the latency and refreshing policies are acceptable, then this might be your choice. You will gain a better performance, reduce locking and even cost of usage if your data provider charges your 'data trip' as perhaps is the case for your mainframe platform.

According to my experience option 1 is mostly used and often abused.

But what do you do every time you turn your gas oven on ?
 

1. You rush to the grocery store and buy exactly one match. Then 'same procedure as....'  
2. You already have a box of matches in your kitchen and just pick up a match out of it. No trip, no extra cost.

 Does this 'parallel' make any sense and can you give it a thought ?

You as BI and .Net Developer

As a BI consultant on a Microsoft based platform, you must have a profound knowledge or experience as a .net developer.

There at least two main reasons for it :

A business application is required by the business and often a .net client sounds like the best choice. That was the case with a Forecast and Monitoring application in my case, as mentioned in a previous blog entry.

.Net coding is an integrated part of all of your BI project types and herby only a few examples :

• SSIS - Script task in VB.net or C#
• SSAS - Store Procedures for calculations or dynamic security
• SSRS - Extending reports with custom code - embedded  or external within an assembly.

Sometimes you may even face the option of developing a .net application not directly related to your magic BI world.

That was the case with my last windows service application, briefly explained her from a business point of view :

http://youtu.be/LIBtYVTLTn8

Lessons from the real DW/BI life

Based on my latest experience from the field, I'd like to mention a few things that are close to my heart.

This is not a thorough check list and I'm not going to plunge you into lots of details for each single issue.

Although your experience may be different, please give them a thought anyway and you're welcome to share your opinion.

 

 

Be driven by adding or enhancing the business value. This is easier said end done, but I've seen that often the focus is on technology.

The long-term vs. short-term goal of your DW/BI team regarding building of an enterprise information infrastructure. And for it's data modeling freaks : there are pros and cons regardless of your usage of an Inmon, Kimball or Data Vault approach or not. Sooner or later - just like in marriage - there is a chance that you will regret it.

The ETL part does not equal the whole DW/BI, but it's a very important part of your DW/BI solution. Although the ETL part is 'transparent'  for the users, I faced situations tendencing to emphasize the metadata part of the ETL or the 'bureaucracy' of data stewardship in stead of listening to the business users and deeply analyse their requirements.   

The Toolset: When it comes to the architecture and product selection for your DW/BI system choose one vendor. If you choose a mix, then there is a huge risk that you'll spend your time integrating the systems or platforms. Microsoft DW/BI platform delivers according to Gartner the best ROI and TOC, but you may feel free to choose IBM, Oracle, SAP or another platform  if you really have strong arguments for it.

The BI solution fails in targeting users of different profiles and the natural flow of information between them: from top level management to analysts and information workers and to fullfill both strategical, tactical and operational business.
I worked on solutions where the ad-hoc or selv-service BI was not taken into account and where the entire solution tried to fulfill the daily operational business insight. No implementation of a Strategy Map, a Six Sigma, a top 5 or bottom 4 culture or even a deep understaing of the notion of KPI or KPI objectives as the underlying pieces of the business perspectives.

Data Mining is still one of the most underestimated parts of the DW/BI solution and I do not have any explanation. According to a IDC study, data mining should be the fastest growing business intelligence segment surpassing any other BI field. Unfortunately I can not recognize this trend. Customers prefer to explorer the yesterday's figures and not to discover patterns of data or predict the metrics with a huge potential to improve their business for a low cost.    
    
Heppy New DW/BI Year.

Compare Cube space in an indirect way

A couple of weeks before, as we migrated our BI solution to different environments, an important task was to ensure that everything was in sync.
This makes sense as we run separate ETL flows on a daily basis in each environment, that in turn pulls out data from their own system of records, not to mention that the SSAS databases were transferred out of our control.
How can we check that both schema and the cube space for the SSAS databases is the same ?
I was not aware of any tool that does this job. On the other side, there are tools that accomplish the task for a relation database.
So, what should do the trick : to compare schema and data for the underlying databases and compare the SSAS XMLA schemas.

I used my Visual Studio Studio 2008 Team Database Edition GDR for the first task
When started, I saw duplicate dropdown items appear under Data menu. I proceeded any way but any attempt failed with an error message like:   
"The operation is not supported within this release."
A schema or data compare between two SQL 2008 R2 databases not supported in a Visual Studio 2008 SP1 release ?
I realized that the incompatibily issue sounds misleading and that the 'error' is related to the duplicate menues.
I was afraid of the idea to repair / uninstall / install the products in the 'right' order but the following steps solved my problem :

• I closed all instances of Visual Studio Team System 2008 editions.
• At the Windows Command Prompt, typed the following command:
• %ProgramFiles%\Microsoft Visual Studio 9.0\DBPro\DBProRepair.exe RemoveDBPro2008 and pressed Enter
• At the Windows Command Prompt, typed the following command: %ProgramFiles%\Microsoft Visual Studio 9.0\Common7\IDE\devenv.exe   

A word of caution here: data compare is naturally based on a primary key constraint for the corresponding tables. If your fact tables do not have a primary key constraint, then you have to figure out another way in order to compare their data.

For the SSAS part, I used the BIDS helper. I strongly recommand this free add-on to BIDS and you can download it from: http://bidshelper.codeplex.com/
I scripted first out my SSAS databases in SSMS by right click -> script database as --> create to --> and saved the XMLA files
Then I used BIDS helper "smart diff" to compare the XMLA files and tracked the differences that may influence the final result from a user perspective..

Writeback bug or feature in SSAS 2008 R2

I wrote on my last project a .net desktop application used for forecast and

monitoring in contact centers as reflected in the blog entry 'Cube writeback with multiple values in one shot'.

I won't reveal all the details of this application but it is based on OWC on a windows form, cube actions intercepted in events fired when user invokes a context menu and finally writing the changes to the cube.

You may recall that cube changes are not written directly to cube cells but to a relational table

called writeback table.

    

The writeback table in my case has columns for all measures in the measure group

- [OfferedCalls_0], [ForecastedOfferedCalls_1], [AdvisorsAbsent_2] ,

 [Distribution_4], [EmployeesPlanned_5], [CallsPerAdvisorDistribution_6] -,

the interecting dimensions :

[SKTime_7] foreign key which points to a Time dimension covering all the 96 quarters of the hours during a day ,

[SKCallFlowDepartment_8] foreign key which points to an organisational dimension  

[SKDate_9] foreign key pointing to the Date Dimension

and two additional columns for auditing purposes - MS_AUDIT_TIME_10] ,  [MS_AUDIT_USER_11] .

The table looks like :

CREATE TABLE [dbo].[WriteTable_Forecast Measures](

 [OfferedCalls_0] [int] NULL,

 [ForecastedOfferedCalls_1] [int] NULL,

 [AdvisorsAbsent_2] [int] NULL,

 [Support_3] [int] NULL,

 [Distribution_4] [float] NULL,

 [EmployeesPlanned_5] [int] NULL,

 [CallsPerAdvisorDistribution_6] [float] NULL,

 [SKTime_7] [bigint] NULL,

 [SKCallFlowDepartment_8] [bigint] NULL,

 [SKDate_9] [bigint] NULL,

 [MS_AUDIT_TIME_10] [datetime] NULL,

 [MS_AUDIT_USER_11] [nvarchar](255) NULL

) ON [fgCurrent]

What it really happens is that the delta changes written to the writeback table inserts a row with a null value for the [SKTime_7] column and this is certainly neither expected or desired.

Cube processing with the default settings fails due to the null key. If you ignore the errors and succeed in processing the cube, the numbers the users entered once are different then the ones displayed by the client, so you have a very serious problem.

You have no influence of this process: playing with the mdx update cube ... statement, changing among the four allocation options ( USE_EQUAL_ALLOCATION is the default option) or

trying to enforce the foreing key constrains will not help you.

So I had to find a work-around based on the following steps : 

• Retrieve the row with the null key in a temporary table with a select into statement.
• Update the writeback table by adding the values retrieved from the temporary table to the values of the corresponding measure for a particular row generated by the client.
• Delete the 'orphan' row.

 as implemented in the bellow script .

select ForecastedOfferedCalls_1 , SKCallFlowDepartment_8 , SKDate_9 , SKTime_7, MS_AUDIT_TIME_10 , MS_AUDIT_USER_11
into #Write_Temp   from dbo.[WriteTable_Forecast Measures]
where SKTime_7 is null

update  dbo.[WriteTable_Forecast Measures]
set ForecastedOfferedCalls_1 = a.ForecastedOfferedCalls_1 + b.ForecastedOfferedCalls_1
from dbo.[WriteTable_Forecast Measures] a, #Write_temp b
where a.SKDate_9 = b.SKDate_9
and a.SKCallFlowDepartment_8 = b.SKCallFlowDepartment_8
and a.MS_AUDIT_TIME_10 = b.MS_AUDIT_TIME_10
and a.MS_AUDIT_USER_11 = b.MS_AUDIT_USER_11
and a.SKTime_7 in ( select SKTime from dim.LocalTimeOfDay
where MNQuarterInterval = '00:00-00:15' )

delete dbo.[WriteTable_Forecast Measures]
where SKTime_7 is null

This seems to solve the problem. But I did not encounter this issue when running the application in the previous SSAS release and this makes me think of the never ending dilemma : bug or feature of the latest SSAS release. This is the question...

 

SCD Type 1 , 2, 3 or 1 + 2 + 3

Question: When you define a SCD (slow changing dimension) as type 1, 2 or 3 ?

I experienced some confusion during the past, so that is why I'd like to put the 'selection' process into perspective, although the discussion may seem a little bit theoretical:
For each SCD dimension :

• Define the business key, the key that identifies your identity, as for instance the Social Security Number ( US) or Cpr. nr (DK) for a customer dimension.
• Pick-up each attribute you have to track changes over time. Do not include her the business key, the surrogate key, and focus only to the attributes that describes, in other words add pieces of information to your business key. ( Addresses, birth day, number of children, marital status , etc. for a customer.)
• Start by diving the attributes you idendified from above in two categories first : attributes where you are not interested at all in tracking the changes (birth date for instance), let' call it category 1 and attributes where you are interested in tracking the changes ( name, adresses, ) , as category 2
• For the attributes in category 2, make a clear decision from the start whether:
• when an attribute value changes you are only interested in the last value. That is an in place override of the previous value and no posibility to track the historical changes. Such an attribute is a type 1 attribute and might be a name or address.
• if you have to keep the historical chages, then you have to choose between two practical ways to implement it :
• type 2 - the most common . You may add either ( StartDate, EndDate) or IsCurrent or even both of them.
• type 3 - less common

Keep in mind that the business requirements decide whether your attribute falls into one category or type.

You will often end with a dimension having attributes of category 1 and category 2 and the latter having both type 1 and type 2 and in rarely cases a mix of type 1, 2 and 3.

So the answer to the original question may sound like:

If you keep all the attributes in your dimension, and your dimension has at least one type 2 attribute , than people call it a SCD type 2 dimension.

    

But what about if you have at least one type 2 and at least one type 3 attribute ?       

It's both a type 2 and type 3 and often not to mention type 1.

In that case, by only calling it type 2, you may hide the representation of the two other types of attributes.

So why not call it : 1 + 2 + 3 ......