Skip navigation.

Practical Business Intelligence

or, How I Solved my Data Warehouse Problems Using SQL Server.

Finding the Fiscal Year for a Given Date Using SQL

Thursday, 7. May 2009, 09:30:36

, , ,

T-SQL provides very useful date management functions such as DATEPART, DATEADD, DATEDIFF and DATENAME. With these functions it's easy to find things out about any given date. For example, to get the name of the month you just need to do:
select datename(m, '2009-05-06')
To get the first date of this year you can use:
select dateadd(yyyy, datediff(yyyy, 0, '2009-05-06'), 0)

This only works for calendar (Jan - Dec) years, though, because SQL Server has no innate understanding of fiscal years in which the first day of the "year" is not Jan 1st.

Many companies have a fiscal or financial year beginning in a different month. In the UK, the tax year starts 1st April and many companies align with that. So what fiscal year does a date fall into and how can this be determined in SQL? This might be particularly important if you have to build a date dimension, and for every date in it you want to have columns for calendar periods and fiscal periods.

The algorithm for date is as follows:

if the calendar date is before the start of the fiscal year, its year began on the first day of the previous fiscal year; if it is on or after the start of the fiscal year, its year began on the first day of the next fiscal year.

This is easiest to imagine with an example. If your fiscal year starts 1st April, all dates in 2009 from January to March belong to the year that started 1st April 2008 and all dates from April to December belong to the year that starts 1st April 2009.

A simple SQL script that can deliver this is:
declare @d datetime
set @d = '2009-03-10'  -- 10th March 2009

select
DATEADD(yyyy, DATEDIFF(yyyy, 0, @d), 0) as [CalendarYear]
,case
when DATEPART(M, @d) >= 4 then DATEADD(mm, 3, DATEADD(yyyy, DATEDIFF(yyyy, 0, @d), 0))
when DATEPART(M, @d) < 4 then DATEADD(mm, -9, DATEADD(yyyy, DATEDIFF(yyyy, 0, @d), 0))
end as [FiscalYear]

What's it doing? It determines if the date is before or after 1st April. If it's after, then the start date of the fiscal year is three months after the start of the calendar year. If it's before, then the start date for the fiscal year is nine months before the start of the calendar year.

Once you have got the correct fiscal year start date, you can easily work out fiscal quarter, month number, week number, day number by using DATEDIFF between the start date and your date. This code will work fine in a stored procedure that creates data for a date dimension (i.e. with a CTF or loop), or just to return periods for a single date.

No comments

Reporting Services 2008, Sharepoint Integrated Mode and Service Pack 1

Friday, 17. April 2009, 20:23:49

You may have noticed that SP1 for SQL Server 2008 has arrived (http://www.microsoft.com/downloads/details.aspx?familyid=66AB3DBB-BF3E-4F46-9559-CCC6A4F9DC19&displaylang=en), and that one of its features is that Report Builder 2.0 can now be distributed as a Click-Once deployment.

If your RS is in SharePoint integrated mode you also need to have the binary installed on the SharePoint server (so that the server can deliver it as a download) and an updated version of the add-in component. There are both available at the following link: http://www.microsoft.com/downloads/details.aspx?FamilyID=58edd0e4-255b-4361-bd1e-e530d5aab78f&displaylang=en.

If anyone has deployments of Report Builder 2.0 already on desktops, there is an update to the standalone installer at http://www.microsoft.com/downloads/details.aspx?familyid=DBDDC9B6-6E19-4D4B-9309-13F62901B0D5&displaylang=en

No comments

Kerberos Issues With Reporting Services and SharePoint in a Distributed Environment

Friday, 20. February 2009, 10:28:02

, , , ...

This seems to cause huge headaches when integrating Reporting Services and SharePoint. After installing the addin correctly and successfully on all your servers, you are unable to complete the setup in Sentral Administration when using Windows Authentication. In "Manage Integration Settings", you are constantly plagued by the error
Server was unable to process request. ---> The request failed with HTTP status 401: Unauthorized
You most likely get this message only when your various services are running under domain accounts rather than the built in accounts like Network Service.

This is caused by incorrectly configured Kerberos somewhere in the system. Kerberos is an authetication mechanism used to pass credentials from one service to another across computers. It's the mechanism which allows SharePoint to authenticate to Reporting Services on a separate server, and which allows a user's credentials to be passed from their browser to SharePoint to Reporting Services to a data source.

The problem with Kerberos is that you don't just click a setting to enable it. It doesn't need to be enabled - it needs to be configured, and there are lots of places the configuration can go wrong. Depending on how your environment was set up, you may find problems in several different places. Here is my list of steps to sort everything out and get it working.

  1. If you have any IIS websites on your Reporting Services machine, and they have an SPN already set, you will have to run Reporting Services web service under the same account as the app pools your other sites are using. This is because the http service can only have one SPN assigned to it (see below). In practical terms this is most likely to be a problem if your RS server also has a SharePoint front end web site which you want to keep active for some reason. In this case you would have to run RS using the same account as you run SharePoint's web app.
  2. Setting SPNs
    Kerberos requires that a Service Principal Name is registered in the domain's Active Directory for the http service. This is explained at this Knowledge Base article. Essentially you need to run 
    setspn -a http/<ReportingServer> <domain>\<user>
    setspn -a http/<ReportingServer>.domain <domain>\<user>
    You need domain admin rights to do this.
  3. Remove duplicate SPNs
    If there is already an SPN for the http service on the Reporting Services box, it must be removed - or you will have to run RS under that account (as above). You can't have one SPN registered twice to separate accounts - even though the setspn tool will quite happily allow you to do it without any kind of warning. Finding duplicate SPNs is tricky, information is available from Microsoft.
  4. Setting Trusts in Active Directory
    With the SPNs set, you now need to set up trusts. A trust is an indication that a particular account is allowed to delegate credentials on another account's behalf (still with me?). Again you need domain admin rights to do this. In Active Directory Users and Computers, open the properties for the account which RS is running under. Open the Delegation tab and enable 'Trust this user/computer for delegation to any service (Kerberos)'. Then repeat this for the Reporting Services computer.
  5. Setting Authentication Providers in SharePoint
    SharePoint needs to be told to use Kerberos. In Central Administration, go to Application Management -> Authentication Providers. Change the web application to the Central Administration app (this is important!) then select the default authentication link. Set to "Integrated Windows Authentication" and "Negotiate (Kerberos)". Save everything and then run IISRESET on the SharePoint web front end host server (not the Reporting Services server). If you have other authentication provider schemes, you will probably need to perform the same steps for them.
  6. Setting IIS to use Kerberos Authentication
    Another trip to the command line on the Reporting Services server. Navigate to c:\Inetpub\AdminScripts and run the following command:
    cscript adsutil.vbs get w3svc\NTAuthenticationProviders
    If this returns "NTLM" or is not set, execute the following command:
    cscript adsutil.vbs set w3svc\NTAuthenticationProviders "Negotiate"
    Then run IISRESET.
  7. Setting Reporting Services to Use Kerberos
    Finally, make sure Reporting Services itself is configured to allow Kerberos. Open the rsreportserver.config file and serach for the AuthernticationTypes node. It should have an entry of
    <RSWindowsNegotiate/ >
    or
    <RSWindowsKerberos/ >
    Save the file and run IISRESET.

Hopefully this checklist will help, integrating SharePoint and Reporting Services can be a frustrating task!

2 Comments

Including a Variance (or Percentage Growth or Change) between two group members in Reporting Services

Monday, 26. January 2009, 15:12:22

, , , ...

In a Reporting Services tablix (or matrix in Reporting Services 2005), it's common to group on a time period such as Year in order to show annual totals. This gives you an easy comparison of figures, such as in the image on the left. This is a valid and useful visualisation of numbers.

However, it doesn't easily enable users to see the percentage variance between the two members of the Year group. If you need to see the amount by which your figures have changed, you need to have it calculated. This can then be displayed next to the annual totals as in the image on the right.

A simple way might be to have the calculation done in your table's data source and displayed as FIRST(Field) rather than SUM(Field). This will work if your query is at the same grouping as your matrix, but because percentages can't be summed, or averaged, or any other type of aggregation to get values at a subtotal or total level, you can't have a total line or change the grouping.

You are left with using an expression in the report. The formula itself is very simple: 
(([This Year]-[Last Year])/[Last Year]) * 100
The problem is that there is no scope which lets you grab the value of one group member - only for the group as a whole or the entire table.

The solution is to put the values you need into variables which can then be used in expressions in the report itself. Two calculated fields are needed to achieve it. They are not added to the query but as calculated fields of the datasource. The first holds values of the two years, but makes the value of the first year a negative number. This can then be SUMmed in order to give a value difference. Add a calculated field with the name "Difference" and add the following code:
=CDbl(iif(Fields!Year.Value=datepart("yyyy", now())-1, (Fields!Sales.Value * -1), Fields!Sales.Value))
In this example the code is dynamically working out the current and previous year so there is no need to change the formula each year. The result is explicitly converted to a double just to be sure we will always work with a consistent data type. The second calculated field is called "FirstValue" only contains values for the first year:
=CDbl(iif(Fields!Year.Value=datepart("yyyy", now())-1, Fields!Sales.Value, 0))


To display the percentage change inside a tablix, add a new column outside the year group. In the column's value cell, add an expression with the following code:
=Sum(Fields!Difference.Value)/Sum(Fields!FirstValue.Value)
This expression can then be placed in any table cell and will give the correct percentage variance at any subtotal level in the table.

To achieve the same result in Reporting Services 2005, you can't mix tables and matrices in the same container so you will need to add a second table immediately beside the matrix and place the expression in it.

2 Comments

No "Reporting Services" Section in Application Management After Installing RS Addin for SharePoint

Wednesday, 14. January 2009, 15:26:28

, , , ...

More pain when trying to configure the Reporting Services Addin for SharePoint 2007 today. In this case it was in a SQL Server 2008 environment, but I am willing to bet the same issue occurs when using the 2005 addin.

The issue is that the addin appears to complete its installation correctly, but once completed there is still no Reporting Services configuration section in the Application Management page of Central Administration.

After trying twice, I had to troubleshoot. To see what the installer is actually doing, you can call it from a command line, as recommended on Microsoft's instructions, by running the command:
rsSharePoint.msi SKIPCA=1
This copies all the required files to the computer without installing the solution to SharePoint. You need to do this on all the SharePoint farm servers which are going to be involved in Reporting Services - at the minimum that will be your Reporting Services server and your SharePoint web front end.

Once the installer finishes on all the computers, navigate to your temp directory (C:\Documents and Settings\<your username>\Local Settings\Temp) on the SharePoint server from a command line. You should see a file named rsCustomAction.exe - this is the executable which deploys and activates the Reporting Services feature to SharePoint. Run it with the command
rsCustomAction.exe /i
and watch as the installation proceeds.

My installation was failing when trying to activate the feature in the root site collection - the Administration site. The error message posted was:
******* user does not have permission to add feature to site collection
Although I was signed on with an account which was a Farm Administrator and a member of the Administrators group on the server.

However, the SharePoint server had been installed and set up by someone else, and when the Admin site collection was created my user was not added as a Site Colleciton Administrator on it. I ensured I was a Site Collection Administrator on all the site collections and ran
rsCustomAction.exe /i
again. The installer proceeded without a problem.

So, the moral of the story: your user must be a site collection administrator on the Administration site collection as well as any other site collections in order to install the add in.

3 Comments

Getting the Top n Members of a Dimension - Excluding the All and Unknown Members.

Monday, 5. January 2009, 09:41:23

, , , ...

The TOPCOUNT function is a simple enough feature of MDX and is very useful for defining things like top 10 customers, top 20 products, etc. The basic syntax for a named set like this would be:
TOPCOUNT([Products].[Product Name],10, SUM([Measures].[Sales Value]))

The behaviour of the set is improved in SQL Server 2008 because the set can be Dynamic, so that the members will be calculated at query time rather than processing time. Therefore the members will change according to the data in the cube.

However, the code above will allow the [All] member as part of the set. In almost every case, [All] would be the top 1 member, so the named set is going to contain [All] and n-1 actual members. To return a top 10 excluding the [All] member, make sure you are specifying the level as well as the dimension and hierarchy:
TOPCOUNT([Products].[Product Name].[Product Name].members,
    10, SUM([Measures].[Sales Value]))

The other dimension member which might be unwanted could be the Unknown, if the dimension contains it. This can be added to an EXCEPT list when defining the set:
TOPCOUNT(EXCEPT[Products].[Product Name].[Product Name].members,
    [Products].[Product Name].[All].[UNKNOWNMEMBER]),
    10, SUM([Measures].[Sales Value]))

3 Comments

Using Named Sets in Analysis Services to Deliver Special Date Ranges to End Users

Friday, 5. December 2008, 16:25:49

, , , ...

Dates can be tricky things for end users to grasp in OLAP applications. Try explaining to a user that Year to Date actually refers to the current member of the date dimension as displayed on their query interface - they glaze over and lose interest immediately. It's not that the concept is difficult for them to understand, it's more that from their point of view, "Year to Date" should mean exactly that - what happened so far this year up to today. And if they open up a report tomorrow, it should reflect the change in date without them having to alter the selections they previously made.

Let's expand on that a bit. A frequent requirement is for a report to display all the activity so far this year, and all the activity last year to the same relative point in time. If you have structured your date dimension properly, it will be very easy for a user to put years on columns, month names on rows and measures in the data area. They can then restrict the years and months manually to this year, last year, and the set of months that have passed this year. It's easy - but it's a manual process.

Why is that a problem? For a start, it will soon be frustrating for end users to change their report definition every month. It will be worse if your reporting is by day rather than by month! Secondly, any kind of automated reporting will not work. This includes scheduled reports... and (crucially) dashboards. Someone will have to update them every day.

What's needed is a simple way of categorising your data so that you can drag "today" onto your analysis and for the system to know what today is. Or "this month", "this year", "the last 12 months", or whatever the requirement might be.

There are plenty of approaches to the problem. You could create calculated members for every measure individually, using the VBA!Now function to get the current date. You could create a utility dimension which contains a member for each of the custom time periods you want to provide and add it to the cube. You could set your date dimension with a default member which is calculated to the current date.

But the most intuitive way for end users to interact with the cube is not with any of these means - and there are technical drawbacks to any of the above methods as well. What users want is quick access to a set of filters which limit their data to the right set of time periods - and for those filters to sit in a logical place in their application, and to do what they say on the tin. Something like this:

The date ranges are nicely placed in the application - inside the date dimension. Therefore they are applicable to any and all measures. They do not rely on any calculated measures, and will perform as well as your aggregation designs provide (the same as if the user were to manually choose the dates). And they do what the average user would expect them to do. A user can then use the named set as a filter in their application (ProClarity, Reporting Services, Excel, etc.) In ProClarity the set can be dragged into the Background area of the setup panel, where the user can slice or combine the members. In Reporting Services it can be used as a filter when creating a data set.

It is achieved by using named sets to select just the time periods which are included. Named sets are nothing particularly complex to understand or to execute, they are simply a pre-defined set of dimension members, specified using MDX in the same way you would specify a set inside an expression. The date sets work as follows: create a set which contains "today", dynamically calculated; apply that set to a hierarchy within the date dimension; create further sets by traversing the dimension hierarchy. So, the sets are stacked on top of one another, but are all based on a set which knows what today is.

To create the set of "today's" date, on the Calculations tab create a new Named Set by clicking the appropriate icon. Give the set a name, then add the magic code which will always return the correct current date:
filter([Date].[Calendar].[Date].Members,
    [Date].[Calendar].CurrentMember.Properties("Date As Int") 
    = Format(Now(), "yyyyMMdd"))

This will have to be adjusted to fit your own date dimension. In this case my date dimension is called "Date", it contains a hierarchy called "Calendar" in which the day level is called "Date". The dimension has an also has an attribute named "Date As Int" (although this is not in the hierarchy) which is the Date member in the format YYYYMMDD.

Once you've added the member and deployed the cube, you can test it by browsing. Add the new named set to the filter, the Date dimension to the rows and add your default measure. You should see that the only member of the date dimension available in the grid is the current date.

The next step tis to apply the result of the set to your main date hierarchy. Create another named set and add code similar to the following:
exists([Date].[Calendar].[Date].Members, [TodaySet]).ITEM(0).ITEM(0)

Modify it to reference your date dimension properly, and replace "[TodaySet]" with whatever you called the first set you created.

You can now create further sets which include specified members from the hierarchy. For example, all the months in the current year to date would be a set defined as (assuming your calendar hierarchy is Year-Month-Date):
[CalendarTodaySet].ITEM(0).ITEM(0).Parent.Parent.FirstChild :
    [CalendarTodaySet].ITEM(0).ITEM(0).Parent.CurrentMember

All the months in the previous year would be:
[CalendarTodaySet].ITEM(0).ITEM(0).Parent.Parent.Lag(1).FirstChild :
    [CalendarTodaySet].ITEM(0).ITEM(0).Parent.CurrentMember.Lag(12)

After creating these two sets you can join them to get all the months in the current year to date plus the all months in last year up to the same month:
{[Previous Year To This Month], [Current Year to This Month]}

All the days in this month so far would be:
[CalendarTodaySet].ITEM(0).ITEM(0).Parent.FirstChild :
    [CalendarTodaySet].ITEM(0).ITEM(0).CurrentMember


I hope this helps - it's a little tricky to get the hang of and to see the possibilities at first. If you get stuck send me an email or message.

5 Comments

Converting an Integer to a Date in SSIS (or any other place you may have VB.net)

Friday, 5. December 2008, 15:27:12

, ,

It's common practice to used meaningful integers as the surrogate key to your date dimension rather than meaningless identity numbers. So, the date December 5th 2008 would have the surrogate key 20081205. One of the advantages to this is that you don't need to look up to the dimension to get the date key when you are populating the fact table: you can just do:
select convert(int, convert(varchar, [MyDate], 112)) as [MyDateKey]
when you connect to the source.

Just occasionally, though, you might need to have date as an actual date for use in a Script - perhaps for doing date calculations, determining weekend events or similar. You could keep a copy of the column in the date data type inside your data flow but that's wasting valuable memory. So, you can convert it directly in a script task as follows:
Dim MyDateKey as Integer = Row.MyDateKey
Dim MyDate = New Date(Left(MyDateKey, 4), Mid(MyDateKey, 5, 2), Right(MyDateKey, 2))

No comments

SQL Server book freebie

Thursday, 18. September 2008, 11:05:51

Microsoft Press have made “Introducing Microsoft SQL Server 2008” available as an e-book and the price is right. It has content for app developers as well as DBAs and BI developers.

http://csna01.libredigital.com/?urss1q2we6 – I thought this was a suspect looking URL but I have checked and it is a genuine Microsoft site, it’s also linked from http://www.microsoft.com/learning/sql/2008/default.mspx#EBOOK for any really paranoid types.

No comments

Configuring Sharepoint 2007 Reporting Services Add-In

Thursday, 17. July 2008, 19:41:27

Some tips on making Reporting Services play nicely in WSS or MOSS 2007.

Things do vary a bit depending on the physical architecture of the installation. The three required components (SQL Server database, Reporting Services and Sharepoint) may be residing on one, two or three different servers which will make a difference to the installation. And if there are two servers, Reporting Services could be on either the SQL server or the Sharepoint server. And the logical architecture matters too - you can run all this on a network without a domain but you will need to manually add at least one user account to each server.

The most important (and worst documented) things to remember are:

  • Don't run the Reporting Services web apps in the default app pool. Move them out to a new app pool and run that app pool under a user account, not a service account. You can run it under the Sharepoint farm account unless there is a security reason not to. Not following this tip is the single biggest reason for integration not working.
  • Remember to install the Sharepoint web part on all your web front ends (once you've installed the add in itself). Not actually essential but frustrating if you can't work out why you can't display reports later.
  • After it's all installed and working, remember to activate the Sharepoint features in any site collection which contains a report library. Without this, the context menus for reports and report models don't appear in document library views.

So, from the top.

Installation

  1. All the SQL Server installations involved need to be at SP2. That includes any box which only has Reporting Services components on it.
  2. If Reporting Services is not on the same server as Sharepoint, install Sharepoint Web Front End and join the server to the existing farm.
  3. Install the Reporting Services Add-In on each of the farm's Web Front End servers. This is just a matter of stepping through the wizard.
  4. Install the web parts by running the following on each of your WFEs:
    stsadm.exe -o addwppack -filename "C:\Program Files\Microsoft SQL Server\90\Tools\Reporting Services\SharePoint\RSWebParts.cab

Setup

Most of the setup is pretty well documented but the steps are:

  1. On the Reporting Services server, run Reporting Services Configuration Manager. Connect to the local deployment of Reporting Services.
  2. On the database setup tab, select the SQL Server which will host the Reporting Services database, create a new database if necessary and change the Server Mode to SharePoint Integrated.
  3. Open IIS Manager. Expand the Application Pools list and see if the Reporting Services apps are running in their own app pool or if they are in the default app pool. This will probably depend on whether you've previously run RS in native mode or if you've changed something in the past.
  4. If the apps are running in DefaultAppPool, create a new app pool and set it's Identity to the same account your Sharepoint farm runs under. Then change the application pool for each of the Report Server web sites to use the new app pool.

    Creating an Application Pool for Reporting Services.
    If your Report Server is not the same machine as your Sharepoint server and you don't have a domain, you will need to create an identical local username/password on the Report Server machine for this.
  5. If your RS apps are already running in their own pool, you just need to change the identity of that pool to match the farm account.
  6. If Sharepoint and Reporting Services are on the same server and Sharepoint is already using port 80, change the port of the RS website.
  7. Restart IIS.
  8. Go back to Reporting Services Configuration and select the Web Service Identity tab. It should have picked up the new app pool, but you need to click Apply.
    image
  9. Select the SharePoint Integration tab and click the link to go to the SharePoint Central Administration website.
  10. In Central Admin, choose the Application tab and then Manage Integration Settings in the Reporting Services section.
  11. Add the address of the Report Server website and click OK. If you changed the site's port number, remember to include that information.
  12. If you want to pass the loged-on user credentials back to Reporting Services through data sources (i.e. when you create data sources in Reporting Services you set them to Windows Authentication), you will need to change the Authentication Mode to "Windows Authentication". To make this work, you will either need to be using Kerberos between all the servers in your farm, or you can just set the NTAuthenticationProviders string in IIS to "NTLM".
    image
  13. Click Grant Database Access, enter the details of the Reporting Services computer and click OK. Enter an appropriate username and password when prompted.
    Note: if this step fails for any reason, you can manually do its work. On the SQL Server, make sure there is a local user group called WSS_WPG, and add the SharePoint farm account to it. If you're not using a domain, create a local user on the SQL Server with the same username/password as the farm account.
  14. Click on Set Server Defaults. If the page opens properly, you know you have set up everything and Reporting Services is running in SharePoint integrated mode. Just click OK to accept the defaults. If the page doesn't open properly, check the following:
    • Both the Reporting Services web sites are running in an application pool which is running under the SharePoint farm account.
    • The SharePoint farm account is in a local user group called WSS_WPG on the SQL Server
    • No typos in the Report Server Web Service URL in Central Admin. Is the port number right?
  15. Once this is working, you can either create a new site collection or use an existing one for your reports. Create at least one library for reports, one library for data sources and one library for report models.
  16. Enable the Report Server Integration Feature in that site collection

And that's all there is to it.

1 Comment

1 2 Next »

Showing posts 1 - 10 of 17.