Friday, December 4, 2009

Where Clause and SSAS Calculations

Today I had to change business rule in our SQL Server Analysis Services 2005 solution. It would be hard to explain the scenario so I use an example.
Scenario: A class contains boys and girls. We need to calculate the passing percentage of students that are boys.

Previously the rule was to get passing percentage of boys.
Total number of boys that pass / Total number of students in class
Correct rule should have been:
Total number of boys that pass / Total number of boys
Yeah, I know this can be done in a giffy in SQL Server but Analysis services is a totally different ball game.

Constraint was that we had no dimension for gender like boys, girls. Instead two columns in fact table (yup, not good implementation) boys and girls with value 0,1.
To work around our constraint we created  Named Query in cube which returned all the dimensions along with every record where boys = 1.
Finally we had to filter measure based upon result dimension (P meaning Pass). So in Calculations tab of cube created a calculation as:

Round(([Measures].[Vw Get All Boys Count], [Result Dim].[Result].&[P])  / [Measures].[Vw Get All Boys Count] * 100,0)

And got the result we were expecting.

Thursday, December 3, 2009

Alerts on SharePoint List

Today I had to troubleshoot a SharePoint environment where alerts were not working.
Firstly this environment was not configured to send mail. So I started by installing IIS's SMTP client component. Configured the relay server.
Then configured the 'Outgoing E-Mail Settings' in Central Administrator.
Added an alert on a custom list.
And upon adding a new item nothing happens... hmm... googled around... eliminated the fail points since this environment was working with anonymous access. And then got it working by:
Disable alerts on the site
stsadm.exe -o setproperty -pn alerts-enabled -pv "false" -url http://problemsite
Enable alerts on the site
 stsadm.exe -o setproperty -pn alerts-enabled -pv "true" -url http://problemsite
Added a new item in the custom list and it working like a charm. Also works when an anonymous user adds a list item.

External Links:
1. Troubleshooting Alerts
2. Configure outgoing e-mail settings