Dynamic Dimension Security MDX Error

Getting errors:

Check MDX script syntax failed because of the following error:
----------------------------------
An error occurred in the MDX script for the dimension attribute permission's allowed member set: The dimension '[Dim Employee]' was not found in the cube when the string, [Dim Employee].[LoginID].[CORP\Name], was parsed.

Solution:
When you set up your role, use the following MDX for the "Allowed member set" for the "User Name" attribute of the "Dim User" Dimension (not the Cube Dimension, the regular Dimension):
STRTOSET("[Dim User].[User Name].["+Username+"]")

You do NOT need to enable Visual Totals!

The other thing you need to set in your role is the "Allowed member set" for the "Dim Reseller" CUBE Dimension. This is important. If you put the security on the regular "Dim Reseller" Dimension, it doesn't seem to work. You have to secure the CUBE Dimension for "Dim Reseller" (I guess this is because only the Cube Dimension knows about the relationships defined for the cube)? Anyway, set the allowed member set to:
EXISTS(
[Dim Reseller].[Dim Reseller].Members,
STRTOSET("[Dim User].[User Name].["+Username+"]"),
'Fact Reseller User'
)

This time you DO need to enable Visual Totals (at least if you want to restrict the FactResellerSales data).

Referenced from Comments from:
http://hccmsbi.blogspot.ca/2007/08/implementing-user-specific-security-in.html

Activate SSRS subscription report on demand

I usually do this to run subscription based report based on SSIS package routine steps. This enables me to trigger subscription based report on demand when data has been refreshed on my Data warehouse.

Run this on Report server.

DECLARE @EVENTData varchar(100)
select @EVENTData=SubscriptionID from dbo.Subscriptions
where Description = ['Report subscription name']

PRINT @EVENTData

exec ReportServerDB.dbo.AddEvent @EventType='TimedSubscription', @EventData=@EVENTData

Issue: Browsing HTML file on Sharepoint 2010 prompt to download

Issue: When you try open a aspx/html/pdf or other such files from your SharePoint Foundation 2010 site; you are prompted for downloading the file rather than opening the file in Browser.

Resolution:

This is an enhanced feature in SharePoint Foundation 2010 and IE8.  Microsoft intentionally locked browser handling to strict to avoid malicious code execution using HTML and Java script object model. In the previous versions of SharePoint; you could open the files directly.  By default; the browser file handling option is set to strict in Central Administration

To change this setting; you need to follow the steps given below.  Please note that the steps needs to be performed in the Central Administration; which is available in your SharePoint Server.

1.    Goto Web Application and Select the WebApplication under which the Site Collection and files are located.

2.   Click on General Settings from the top “Ribbon”.

3.   Select General Setting

4.   Locate the section “Browser File Handling”

5.   Change it to Permissive.

Using xml path to combine multiple rows into single column

Can combine multiple row values into a single column using xml path function

select distinct employee_manager_id,
(SELECT ',' + h1.employee_id from hr.dim_employee_reporting h1
where h1.employee_manager_id=h.employee_manager_id for xml path('')) as employees
from hr.dim_employee_reporting h
where employee_manager_id = 'xxxxx'

Source reference: http://bisqlserver.rdacorp.com/2011/10/using-for-xml-path-to-combine-multiple.html

Changing DSV file name of and existing cube

One of my requirement was to create a new cube from existing cube (structure of the cube was important for me) and point it to a different Data source view (since i wanted to extend the existing DSV to accomodate additional changes while retaining the old cube as it is.).  Unfortunately the DataSourceViewID property for the cube/ dimensions are greyed out from BIDS.

So the trick is to open the Cube in Code view and search for DataSourceViewID element.  Once found change the value to new DSV file name.


<Source dwd:design-time-name="b225080e-6fcc-4a40-8a7a-7233084ee828">
    <DataSourceViewID>XXXXXX</DataSourceViewID>
  </Source >


Thats it now when you go on the UI and choose to Edit Data Source View for the cube or dimension it would take you to the new DSV file name you have associated it to.

Excel 2007 error : The cube might have been reorganized or changed on the server.

While trying to deploy a cube and opening in Excel 2007 (Pivot table). I encountered this error:

Excel was unable to get necessary information about this cube. The cube might have been reorganized or changed on the server. 

Contact the OLAP cube administrator and, if necessary, set up a new data source to connect to the cube.

I checked the datasource it was all good.  I could further access the processed cube through Proclarity and SSMS.  But excel wouldnt accept it.


So I suspected that it might be one of the calculated member in my cube that might be causing this issue.  And viola there it was one of the named set created in my cube had an incorrect Hierarchy path.  I guess Proclarity and SSMS ignored this but excel was bit strict on that.  Fixed the Hierarchy path, reprocessed the cube and bang!! I am back on business.

Other reason for this as explained in MS KB articles may be due to invalid characters in attributes:

http://support.microsoft.com/kb/210806

or problem with translation setting

http://support.microsoft.com/kb/931388/en-us

In my case all these seems be ok.  The issue was the faulty named set.

Insert tid bits from SQL Pass 2011

Found this useful bit of tip from SQL Pass 2011 presentation.

create table dbo.numbers
(
Numbers int primary key
);

SELECT TOP(1000) RN = ROW_NUMBER() OVER
(ORDER BY c1.[object_id])
FROM sys.columns c1
cross join sys.columns AS c2
cross join sys.columns as c3
order by c1.[object_id]

Alternative Time Intelligence implementation reference link

Very interesting blog by Marco Russo about alternative time intelligence implementation (YTD etc).

http://sqlblog.com/blogs/marco_russo/archive/2007/09/02/datetool-dimension-an-alternative-time-intelligence-implementation.aspx