Archive for September, 2011

I have a report requirement to display a graph of the number of medical treatment injuries (MTI) for the last 12 months from the selected month together with the target value. The target value is set against a year, an injury type and any level in the organisation (i.e. can be against a company, a business unit or a division).

For example, the target for the MTI injuries for year 2010 is 10 and for year 2011 is 13. The target value should not have a straight horizontal line in the graph.

 This target value cannot be rolled up. Luckily my client is using SQL 2008R2 enterprise edition, which allows semi-additive aggregation such as  Average Over Time, First Value, Last Value, First Non-Empty Value, Last Non-Empty Value etc.

Setting the target to aggregrate using “None” in the cube does not bring back any value at all. Setting it to Last non-empty value is the answer so that it will only pick the last non empty value of the target for the particular level in the organisation for a particular injury type and year.

Read Full Post »

I have a report requirement to display the number of accident near miss events currently open for more than 30 days from the selected month in the @DateMonth parameter. So there is a need to get the last day of the month selected in the @DateMonth parameter. Also a need to get a date difference between the date when the accident occurred and the last day of the month selected.

My MDX code to do the above:

SET [LastDayOfMonth] AS CLOSINGPERIOD([Date].[Monthly].[Date], STRTOMEMBER(@DateMonth, CONSTRAINED)  )

MEMBER [Measures].[DateDiff] AS
‘DATEDIFF(“d”,[Date].[Monthly].CurrentMember.Name, [LastDayOfMonth].Item(0).MemberValue )’

MEMBER [Measures].[Gap] AS [LastDayOfMonth].Item(0).MemberValue – [Date].[Monthly].CurrentMember.MemberValue

select NON EMPTY {[Measures].[Events Count], [Measures].[DateDiff] , [Measures].[Gap]} on columns
  ,NON EMPTY [Date].[Monthly].[Date] ON ROWS
from Cube
WHERE [Event Types].[Event Type].&[Health & Safety]&[Health & Safety Near Miss]

The item(0) is to pick the first item in the array (the [LastDayOfMonth] calculated set).

To get a date difference between the accident date and today’s date, use Now().

MEMBER [Measures].[DateDiff] AS
‘DATEDIFF(“d”,[Date].[Monthly].CurrentMember.Name, Now()’




Read Full Post »

I set up my ETL process to run every hour daily and when it fails, I get an email notification. However, when the ETL failed, I did not get any emails at all. So I went in there and check what went wrong this time. SQL Server Agent log returns with “An attempt was made to send an email when no email session has been established”.  This is a bit strange as I still get emails notification when ETL process has warnings/errors. So I am completely certain that the email works.

My database mail has been configured properly  because the test email from the database mail has been successful.  Google returned with a few blogs that say there is a need to set up an “operator” for the SQL Server Agent. So I went in there and created a new operator, gave it my name and my email address. Next, right click on the SQL Server Agent, then select “Alert System”. Tick “Enable mail profile. Choose the appropriate Mail System (I chose Database Mail because SQL Mail will become obsolete in the future) and Mail profile (i.e. this is the profile name that I created in the Database Mail). I have also ticked “enable fail-safe operator” to myself and notify using Email.

The last step that I had to then was to check my SQL job setting. Under the “Notifications” section, I ticked the “Email” option so that when the SQL job fails, email to myself. This time I could select my name (i.e. operator)  from the drop down list.

OK..all set. Ran my SQL job for the ETL process that I know would fail. I still didn’t get any emails… hmmm..

Did a bit of research on Google and the clue for this is I have to restart the SQL Server Agent! So I restarted the SQL Server Agent and ran the ETL process again. I finally got the email notification when the SQL job/ETL process failed. Yay 🙂

Read Full Post »

It took me a while to figure this out for my client in South Africa.  I set a report subscription to email out the report to a list of users in South Africa but it has failed with an error saying one of the email addresses is not valid. So I removed the whole list of user’s email addresses and set it to only email the report to my colleague in South Africa and this has failed as well. SQL Agent logs returned with an error saying “Open Relay not allowed”. My first suspicion was relaying setting but I needed to confirm this first.

So I checked the Email Setting in the Reporting Services Configuration Manager and used the Send To email address as the email recipient in the report subscription. This time it worked. This confirmed that this is an issue with the email relay set up on the SMTP server.

Read Full Post »

%d bloggers like this: