Sunday, March 17, 2013

Programmatically Create PerformancePoint Data Source

For a SharePoint BI PerformancePoint project, we need to package a BI solution in a solution package and programmatically create PerformancePoint data source.
Using Mointoring API, the task seems easy.



using Microsoft.PerformancePoint.Scorecards;
using Microsoft.PerformancePoint.Scorecards.Indicators;
 
 
namespace PerformancePointAPI
{
    class Program
    {
        const string ServerName = http://my_desktop_pc;
        
 
        const string webServiceUrl = "/_vti_bin/pps/PPSAuthoringService.asmx";
        const string connectionListUrl = "/Data Connections for PerformancePoint";

        private static void CreateDataSource_Bad()
        {
            string url = ServerName + webServiceUrl;
            IBIMonitoringAuthoring biService = BIMonitoringAuthoringServiceProxy.CreateInstance(url);
 
            //Create data source object
            DataSource dataCube = new DataSource("AW_Data_Cube");
            dataCube.ServerName = "myserver1";
            dataCube.DatabaseName = "demo_db";
            dataCube.CubeName = "my_data_warehouse";
            dataCube.ConnectionContext = ConnectionContext.ConnectAsSharedUser;
            dataCube.FormattingDimensionName = "Measures";
            dataCube.MinutesToCache = 10;
            dataCube.CustomTimeIntelligenceSettings = "";            
           
            biService.CreateDataSource(connectionListUrl, dataCube);
        }
        static void Main(string[] args)
        {
            CreateDataSource_Bad();
        }

After running this code from a console app, an exception was thrown at the line that creates the data source.

System.Web.Services.Protocols.SoapException was unhandled
  Message=Server was unable to process request. ---> You do not have permissions to create a data source in this document library.  Additional details have been logged for your administrator.
  Source=System.Web.Services
  Actor=""
  Lang=""
  Node=""
  Role=""
  StackTrace:
       at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)
       at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
       at Microsoft.PerformancePoint.Scorecards.BIMonitoringAuthoringServiceProxy.CreateDataSource(String listUrl, DataSource dataSource)
       at PerformancePointAPI.Program.CreateDataSource_Bad() in C:\Users\ethan.deng\Desktop\PerformancePointAPI\Program.cs:line 65
       at PerformancePointAPI.Program.Main(String[] args) in C:\Users\ethan.deng\Desktop\PerformancePointAPI\Program.cs:line 26
       at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException:


Looking into the SharePoint log, it has

07/03/2012 16:06:39.42  w3wp.exe (0x15E4)                        0x287C PerformancePoint Service       PerformancePoint Services      19 Critical The user "xyzconsulting\ethan.deng" attempted to save an item in the following location: http://dkstp-spdev2-pc/Data Connections for PerformancePoint  Verify that the location exists and that the user has the "Add Items" permission.  Exception details: Microsoft.PerformancePoint.Scorecards.BpmException: Failed to create the item ''.      at Microsoft.PerformancePoint.Scorecards.Store.Dao.SPDocDao.Create(ISPFcoCache cache, Guid webSiteID, String listUrl, FirstClassElement element)     at Microsoft.PerformancePoint.Scorecards.Store.SPDataStore.SPDaoCreate(String listUrl, FirstClassElement element, String spObjectTypeId) 005c2eb9-a8bb-4d39-832f-7a71b0d4b52f
07


At the first glance, the hightlighted log made it obvious that it was an authenticatino/authorization issue (which sent me down the wrong path for quite a few hours). I started to chase the issue down the path of the authentication issue. I used all the weapons I can have including: Event Viewer, Fiddler, SharePoint logging, SQL Server Profiler, ... but could not figure out why my account who has full permissions to OS, SharePoint and database couldn't create the data source file at the destination connection library. Google search didn't return much clue either.

As the last resort, I fired up my ultimate weapon ".NET Reflector" and chased down the source code of the error

Microsoft.PerformancePoint.Scorecards.Store.Dao.SPDocDao.Create(ISPFcoCache cache, Guid webSiteID, String listUrl, FirstClassElement element)

        public override FirstClassElement Create(ISPFcoCache cache, Guid webSiteID, string listUrl, FirstClassElement element)
        {
            SPSite targetSite = null;
            SPWeb targetWeb = null;
            FirstClassElement element4;
            if (element == null)
            {
                throw new ArgumentNullException("element");
            }
            if (string.IsNullOrEmpty(listUrl))
            {
                throw new ArgumentNullException("listUrl");
            }
            NameValidationHelper.NameValidationResultEnum validationError = NameValidationHelper.ValidateName(element.GetType(), element.Name.Text);
            if (validationError != NameValidationHelper.NameValidationResultEnum.NameIsValid)
            {
                string nameValidationErrorMessage = NameValidationHelper.GetNameValidationErrorMessage(validationError);
                throw new BpmException(string.Format(CultureInfo.CurrentCulture, ClientResources.GetString("SPItemCreateFailed"), new object[] { element.Name.Text, nameValidationErrorMessage }), ErrorCode.FCOCreateFailed);
            }
            try
            {
                SPList targetList = SPHelper.GetTargetList(webSiteID, listUrl, ref targetSite, ref targetWeb);
                if (targetList == null)
                {
                    ServerUtils.UnauthorizedAccessDetected = true;
                    throw new BpmException(ClientResources.GetString("SPListNotExist"), ErrorCode.FCOCreateFailedListAccess);
                }
                if (ServerUtils.AllowUnsafeUpdates)
                {
                    targetList.ParentWeb.AllowUnsafeUpdates = true;
                }
                if ((GetDocLib(targetList) == null) || (element.ContentType != FCOContentType.PpsDataSource))
                {
                    throw new BpmException(ClientResources.GetString("SPNotDocLib"), ErrorCode.FCOCreateFailedListType);
                }
                FirstClassElement element2 = (FirstClassElement)element.Clone();
                element2.Location = new RepositoryLocation(listUrl);
                SPContentTypeId cTypeId = new SPContentTypeId("0x01");
                if (!SPListItemDao.GetContentTypeId(element2, ref cTypeId) || !SPListItemDao.ListSupportsContent(targetList, cTypeId))
                {
                    throw new BpmException(ClientResources.GetString("SPTypeNotSupported"), ErrorCode.FCOCreateFailedListContentType);
                }

Pondering on this code  for a while and tried a few errors by purposely passing empty and null parameters to the method from my code, I suddenly realized that the code never passed the first few lines which do name validation.

I manually triggerred

ClientResources.GetString("SPItemCreateFailed")

and it returns "Failed to create the item '{0}'. {1}"

which proved my guess. Now looking at the error message again.

Exception details: Microsoft.PerformancePoint.Scorecards.BpmException: Failed to create the item ''.    

and compared it to

throw new BpmException(string.Format(CultureInfo.CurrentCulture, ClientResources.GetString("SPItemCreateFailed"), new object[] { element.Name.Text, nameValidationErrorMessage }), ErrorCode.FCOCreateFailed);      
I realized that the real error may be from the "element.Name.Text" which is the empty '' in the error message.

It was the empty string of "element.Name.Text" that caused the name validation failed and exception was thrown, caught and handled by

Microsoft.PerformancePoint.Scorecards.Store.SPDataStore.SPDaoCreate(String listUrl, FirstClassElement element, String spObjectTypeId)

This method mistakenly wrapped the inner error message within a message about "Access Error" which is misleading.

The final solution is to always set the DataSource "Name.Text" property before creating the data source.
 
        private static void CreateDataSource_Good()
        {
            string url = ServerName + webServiceUrl;
 
            IBIMonitoringAuthoring biService = BIMonitoringAuthoringServiceProxy.CreateInstance(url);
 
            //Create data source object
            DataSource dataCube = new DataSource("AW_Data_Cube");
            dataCube.Name.Text = "AW_Data_Cube";
 
            dataCube.ServerName = "my_server_1";
            dataCube.DatabaseName = "demo_db";
            dataCube.CubeName = "my_data_warehouse";
            dataCube.ConnectionContext = ConnectionContext.ConnectAsSharedUser;
            dataCube.FormattingDimensionName = "Measures";
            dataCube.MinutesToCache = 10;
            dataCube.CustomTimeIntelligenceSettings = "";
            string temp = dataCube.Name.Text;
 
 
            biService.CreateDataSource(connectionListUrl, dataCube);
        }

No comments:

Post a Comment