Fix: PowerPivot for SharePoint Data Refresh

ImageError 

Fix:

  1. On the Analysis Services Server running in SharePoint mode, Add the Analysis Services service account to the “Act as part of the operating system” privilege:
    1. Run “secpol.msc”
    2. Click Local Security Policy, then click Local policies, and then click User rights assignment.
    3. Add the service account.
  2. Restart Excel Services and reboot the Analysis Services server.

Delegation from the Excel Services service account or from Claims to Windows token service (C2WTS) to the Analysis services instance is not required. Therefore no configuration for KCD from Excel Services or C2WTS to PowerPivot AS service is necessary. Note: If the backend data source is on the same server as the Analysis Services instance, delegation is not required.

Querying Active Directory from SQL Server

Sometimes source systems do not store Windows credentials as an attribute of a person, and when that happens security becomes an interesting challenge.

For this project, I needed to retrieve the EmployeeNumber attribute from Active Directory to be able to link the user back to the source system (very useful for Reporting, but same logic applies for any service from the SQL Server BI stack).

First we need to create a ‘Linked Server’ from a SQL Server instance with the following system stored prod:

EXEC sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces',
'ADSDSOObject', 'adsdatasource'
GO

After you just create a Data Source pointing to the instance where you added that Linked Server and start querying the attributes you need from the Active Directory domain you need:

SELECT 
     WinUsername
     ,EmployeeNumber 
FROM OPENQUERY(ADSI,
    'SELECT 
          sAMAccountName
          ,employeeNumber
     FROM 
          ''LDAP://ou=users,ou=YourDomain,dc=YourDomainControler''
     WHERE 
          objectCategory = ''Person'' 
          AND objectClass= ''user'' 
          AND sAMAccountName='''+ YourUsername + '''')

If you need to get groups:

SELECT 
     User
     ,GroupName
FROM OPENQUERY( ADSI, 
     'SELECT 
          Name
          ,distinguishedname
     FROM 
          ''LDAP://ou=YourDomain,dc=YourDomainControler'' 
     WHERE 
          objectCategory = ''GROUP'' 
          AND CN = ''* *''')

If you need to know the people that belong to a specific Group:

SELECT 
     samaccountname
     ,Name
FROM OPENQUERY(ADSI, 
     'SELECT 
          samaccountname
          ,Name
     FROM 
          ''LDAP://ou=YourDomain,dc=YourDomainControler'' 
     WHERE 
          objectCategory = ''user'' 
          AND memberof = ''CN=NameOfTheGroup,OU=Groups,OU=YourDomain,DC=YourDomainControler''')

Passing Multi-value Parameters to a MDX Query in SSRS

Let’s say our report needed ProductCategory as a parameter.  The ProductCategoryKey in the relational database for Bikes is 1, and our cube member for Bikes is [Product].[Category].&[1].  So we can transform our parameter value to pass into our mdx query like so:

“[Product].[Category].&[”+Cstr(Parameters!ProductCategory.Value) + “]”

Simple right?

BUT, I needed to be able to pass multiple values, which meant I had to take some extra steps before I could parse my string.  If ProductCategory is a multi-value parameter, then Parameters!ProductCategory.Value is an object – an array of values.  So we need to create a single string out of that array, format that string, and then break it up again back into an array since that is what the MDX query is expecting.  Whew!

Here’s the magic:

Split(“[Product].[Category].&["+Replace(Join(Parameters!ProductCategory.Value,"],”)+”]”,”,”,”,[Product].[Category].&["),",")

Cool eh? but, what’s happening here?

Join() is a handy little function that takes our array and joins the values into a string.  We can delimit the values with a string, in this case “],”, like so:

Join(Parameters!ProductCategory.Value, “],”)

Bikes, Components, and Clothing for our parameter values, our string after using the join function will be“1],2],3″.  We can then add an ending bracket to the end via simple string concatenation so now we have “1],2],3]”.

Applying the Replace() function, we replace “,” with “,[Product].[Category].&[“ and now we have “1],[Product].[Category].&[2],[Product].[Category].&[3]“.  Finish with one final string concatenation in the front and we have “[Product].[Category].&1],[Product].[Category].&[2],[Product].[Category].&[3]“

Unfortunately we can’t just pass this into our MDX query.  It’s expecting an array object, not a string!  But luckily, the Split() function is the counterpart to Join() and breaks up our string into that array object we want via the commas as a delimiter.

MDS HttpWebRequest WebException RemoteServer issue

Here is a small description of the issue:

I have in my box Windows Server 2012 64-bit, SQL Server 2012 SP1 64-bit and SharePoint 2013.

After installing and configuring MDS on my server, I can go to the web app’s home page but if I click on the Explorer (or pretty much anything running SilverLight), I get the following error:

MDS_HttpWebRequest_WebException_RemoteServer

 

Then I downloaded and install the Excel Add-in for Excel 2013, but when I try to connect to the server, I’m getting this error:

MDS_Add_In_Excel

 

After hours of more research, and lots of patience I finally was able to solve a very weird issue with Master Data Services.

It turns out that that for some odd reason, the applicationHost.config file located in the windows/system32/inetsrv/config folder  was missing a XML tag for the MDS ‘service’.

1) Make sure you have all the MDS Web Application requirements installed on your server: http://msdn.microsoft.com/en-us/library/ee633744.aspx

2) Make sure you created your site with the MDS Configuration Manager and that you in fact can browse the Home Page.

3) You need to have Admin permissions and edit the windows/system32/inetsrv/config/applicationHost.config file to make sure it contains the following tags:

<location path="MDS">
 <system.webServer>
  <security>
   <authentication>
    <digestAuthentication enabled="false" />
    <basicAuthentication enabled="false" />
    <anonymousAuthentication enabled="false" />
    <windowsAuthentication enabled="true" />
   </authentication>
  </security>
 </system.webServer>
</location>

<location path="MDS/Service">
 <system.webServer>
  <security>
   <authentication>
    <basicAuthentication enabled="false" />
    <anonymousAuthentication enabled="true" />
    <windowsAuthentication enabled="true" />
   </authentication>
  </security>
 </system.webServer>
</location>

If I have to guess what exactly caused the issue, I’ll say that the ‘Service’ tag gets created the first time you create a site, so if you for any reason end up deleting or creating another site, IIS is actually not updating this file. But, that’s just my guess.