SSAS file extentions (exclude from anti-virusscanner)

Here’s a complete list of all the file extentions which SSAS uses. You may want to exclude these when you’re running an anti-virus scanner on your server:
http://phoebix.com/2013/07/23/excluding-analysis-services-files-from-anti-virus-scanning/


.ahstore
.asstore
.asstoreidx
.astore
.bin
.bsstore
.bstore
.cub
.data
.det
.dim
.ds
.dstore
.hdr
.hstore
.khstore
.ksstore
.kstore
.lstore
.map
.ostore
.prt
.sstore
.xml

A list of all the ‘Data Directories‘ can be found here:
http://technet.microsoft.com/en-us/library/cc281997.aspx








SSRS Parameter Expression: Previous Workday

Depends on whether your company works on a Saturday, but one of these SSRS Expressions will do the trick:

Basically WEEKDAY() will return the day number of the week, which will return:
– Sunday = 1
– Monday = 2
– etc.
(Doesn’t matter which Language (e.g. en-GB, etc.) you’ve set in the report)

So if you would like to select the previous workday (including Saturdays):

=IIF(WEEKDAY(TODAY())=2,
DATEADD("d",-CINT(WEEKDAY(TODAY())),TODAY()),
DATEADD("d",-1,TODAY())
)

‘In case it’s Monday, select Today’s date minus 2 days. Otherwise return yesterday’
If you would like to exclude the entire weekend:

=IIF(WEEKDAY(TODAY())=1 OR WEEKDAY(TODAY())=2,
DATEADD("d",-CINT(WEEKDAY(TODAY()))-1,TODAY()),
DATEADD("d",-1,TODAY())
)









SSRS: SQL Agent Job Schedule Timeline report

Inspired by the SQLjobvis application, which works great btw.
But I wanted to make it a little neater and (of course) have the ability to schedule it daily 😉

Thus….here’s the result:

sql-agent-job-schedule-timeline-report

It displays the SQL Agent Job History of the past 24-hours, but you might want to increase that or even use Dynamic-SQL with Linked-Servers to have the option to select different SQL Servers.

Important note:
In the query I’ve used the new TRY_CONVERT function of SQL Server 2012 (more info here). In case you want to use this report on 2005 or 2008 (R2), look for the function in the query and remove that part (prefix string for report subscriptions).
You may want to use the attached stored procedure or stick with the report which has the query built-in.
Also in the ZIP-file you find a SQL query that is compatible with SQL Server 2000 (but is of course a bit slower).

If you like the report, show your appreciation by sharing it on your blog or social media.
Have fun with it!

Download:
Download it here


Here’s another report of mine which you might find interesting:
SSRS: SQL Agent Job Status report

sql-agent-jobs-report








SQL Server Spatial Art

Yesterday I attended Hope Foley‘s webinar during 24 Hours of PASS, thanks for that!

A few examples:
Here’s Michael Coles Christmas card of 2009:
http://sqlblog.com/blogs/michael_coles/archive/2009/12/22/merry-christmas.aspx
sql-server-christmas-card-by-michael-coles

Here’s a great example by Michael J Swart:
http://michaeljswart.com/2010/02/more-images-from-the-spatial-results-tab/

sql-server-christmas-card-by-michael-j-swart

How can I create such art?
Well Alex Whittles explains how you could accomplish this on his blog:
http://www.purplefrogsystems.com/blog/2011/05/sql-server-art-using-spatial-data/

Besides that it’s cool, a more practical use for spatial data is for example if you’re in the hotel of conference business.
Imagine a report that displays your floor layout and also if a room is occupied or not? Perhaps also to display block-reservations (groups), like for example for SQL Saturday or when your local footbal club is staying at your hotel?

Here’s an example:
http://social.msdn.microsoft.com/forums/sqlserver/en-US/82706ecf-f932-417a-bb6f-b7b26a53ace6/drawingtracing-tools-for-a-blueprint

sql-server-spatial-floorplan








SSRS: SQL Agent Job Status report

I’ve created a report that shows a little more information than the SQL Agent Activity Monitor and it can be scheduled via SSRS:

sql-agent-jobs-report

Download:
Click here to download the files:
SSRSSQLAgentJobsReport.zip



Here’s another report of mine which you might find interesting:
SSRS: SQL Agent Job Schedule Timeline report

sql-agent-job-schedule-timeline-report








SSRS/MDX Parameter values based on a (filtered) dimension (which actually relates to records in the facts)

[Dim Period] dimension values:
For this demo, I have a dimension called [Dim Period] in my SSAS cube which looks like:

ssas-cube-dim-period-id-values


All values
So here’s how you could diplays all values (children) of a certain dimension:
ssas-cube-dim-period-id-values-all-code

ssas-cube-dim-period-id-values-all-results



Filtered by NOW()/FORMAT

([Dim Period].[Id].&[200701] : StrToMember("[Dim Period].[Id].&[" + Format(Now(), "yyyyMM") + "]")


ssas-cube-dim-period-id-values-filtered-by-now-code

ssas-cube-dim-period-id-values-filtered-by-now-results



Filtered by HAVING

HAVING NOT ISEMPTY([Measures].[--Value--])


ssas-cube-dim-period-id-values-filtered-by-having-code

ssas-cube-dim-period-id-values-filtered-by-now-results



Code
Here’s the MDX script I used:

--All members in the [Dim Period] dimension:
WITH
MEMBER [Measures].[ParameterCaption] AS [Dim Period].[Period Description].CURRENTMEMBER.MEMBER_CAPTION
MEMBER [Measures].[ParameterValue] AS [Dim Period].[Id].CURRENTMEMBER.UNIQUENAME
SELECT
{
[Measures].[ParameterCaption]
,[Measures].[ParameterValue]
} ON COLUMNS,
ORDER([Dim Period].[Id].CHILDREN,[Dim Period].[Id].CurrentMember.Member_Name,DESC) ON ROWS
FROM [CUBE]

--Filtered based on the current date:
WITH
MEMBER [Measures].[ParameterCaption] AS [Dim Period].[Period Description].CURRENTMEMBER.MEMBER_CAPTION
MEMBER [Measures].[ParameterValue] AS [Dim Period].[Id].CURRENTMEMBER.UNIQUENAME
SELECT
{
[Measures].[ParameterCaption]
,[Measures].[ParameterValue]
} ON COLUMNS,
ORDER([Dim Period].[Id].CHILDREN,[Dim Period].[Id].CurrentMember.Member_Name,DESC) ON ROWS
FROM
(
SELECT
--Here's a trick that will work on date/time values:
([Dim Period].[Id].&[200701] : StrToMember("[Dim Period].[Id].&[" + Format(Now(), "yyyyMM") + "]")
) ON COLUMNS
FROM [CUBE]
)

--But for other non time related dimensional members, you need to a different method:

WITH
MEMBER [Measures].[ParameterCaption] AS [Dim Period].[Period Description].CURRENTMEMBER.MEMBER_CAPTION
MEMBER [Measures].[ParameterValue] AS [Dim Period].[Id].CURRENTMEMBER.UNIQUENAME
SELECT
{
[Measures].[ParameterCaption]
,[Measures].[ParameterValue]
} ON COLUMNS,
ORDER([Dim Period].[Id].CHILDREN,[Dim Period].[Id].CurrentMember.Member_Name,DESC)
HAVING NOT ISEMPTY([Measures].[BS Balance])
ON ROWS
FROM [CUBE]