Using UNPIVOT in SQL Server

June 16, 2010 Leave a comment

Today I had to write a procedure to unpivot a table with an unknown number of columns / column names, I’ve used a combination of user defined function and dynamic sql to build a sql unpivot statement and then execute it…

 

First create function…

 

CREATE FUNCTION [dbo].[Udfgetcolumnnames] (@Table VARCHAR(100))
RETURNS VARCHAR(MAX)
AS
  BEGIN
      DECLARE @str VARCHAR(MAX)

      SET @str =”

      SELECT @str = @str + column_name + ‘,’ frominformation_schema.columns
      WHERE  table_name = @Table
             AND column_name NOT IN( ‘ShiftStart’, ‘AgentName’, ‘Date’ )

      SET @str =Substring(@str, 1, Len(@str) – 1)

      RETURN @str
  END  

Then use it to help define the SQL…

DECLARE @sql NVARCHAR(MAX)

SET @sql =”
SET @sql =
‘SELECT AgentName,  Date, pvt.Interval, pvt.ActivityCode FROM ActualAgentActivity UNPIVOT ( ActivityCode for Interval in (‘ +
dbo.Udfgetcolumnnames(‘ActualAgentActivity’) + ‘)  ) as pvt WHERE pvt.ActivityCode <> ”.”’

EXEC Sp_executesql @sql 

 

You will notice I hardcoded some values in my function – Trying to add this functionality as an extra parameter/s was a complete nightmare and I ran out of time.  If anyone has a suggestion on how this could be made more generic I would love to hear it (given that there could be any number of excluded columns)

Filed under Work

Getting a sharepoint page to refresh automatically

June 15, 2010 Leave a comment

Enabling your SharePoint page to refresh automatically after x seconds is as simple as adding a Content Editor web part and adding the following javascript to it in the source editor:


<script type="text/javascript" language="javascript">
var reloadTimer = null;
var sURL = unescape(window.location.pathname);

function setReloadTime(secs)
{ if (arguments.length == 1)
   { if (reloadTimer) clearTimeout(reloadTimer);
       reloadTimer = setTimeout("setReloadTime()", Math.ceil(parseFloat(secs)*1000));
   }
   else
   { reloadTimer = null;
     location.reload(true);
     window.location.replace( sURL );
   }
}

setReloadTime(30);
</script>
Thanks for this go to http://drewmace.blogspot.com/2008/02/auto-refresh-sharepoint-page.html

Filed under Work

Instant SQL formatter

May 25, 2010 Leave a comment

Does what it says on the tin – http://www.dpriver.com/pp/sqlformat.htm?ref=g_wangz

Filed under Work

Getting the definition of a view out of oracle

May 25, 2010 Leave a comment

I know I’ll have to do this again one day so….
 
SQL> set long 10000

SQL> select TEXT
  2  FROM DBA_VIEWS
  3  where OWNER = ‘<owner_name>’
  4  and VIEW_NAME  = ‘<view_name>’;

 

Filed under Work

How to add a calculated measure to SSAS cube using XMLA

May 20, 2010 Leave a comment

Needed a way of programatically adding a calculated measure to a cube after the cube had been built and processed, the xmla below will do this – Run in SQL management studio as xmla query.
 
<Alter AllowCreate="true" ObjectExpansion="ExpandFull" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Object>
    <DatabaseID>CVCC</DatabaseID>
    <CubeID>CVCC</CubeID>
    <MdxScriptID>MDXScript</MdxScriptID>
  </Object>
  <ObjectDefinition>
    <MdxScript>
      <ID>MDXScript</ID>
      <Name>MDXScript</Name>
      <Commands>
        <Command>
          <Text> <!–if you want to figure out what to paste below build the calculation in visual studio then look at the ‘script view’–>
            CALCULATE;
            CREATE MEMBER CURRENTCUBE.[MEASURES].PcntEscalations
            AS [Measures].[NumEscalations]/[Measures].[NumCalls],
            FORMAT_STRING = "Percent",
            NON_EMPTY_BEHAVIOR = { [NumCalls], [NumEscalations] },
            VISIBLE = 1  ;
          </Text>
        </Command>
      </Commands>
    </MdxScript>
  </ObjectDefinition>
</Alter>

Filed under Work

Sprocs to create Date and Time Dimensions

May 19, 2010 1 Comment

Working a project and needed generic date and time dimensions, wrote the following stored procedures, blogged here for future use…

 

The date one accepts 2 parameters a start date and an end date, it then creates a row for each day between these dates.

 

We had a bit of a discussion on the time one about whether it should go to the minute or second level so I’ve included a parameter called @grain which should be set to either ‘second’ or ‘minute’, so you can choose which level you need.

 

SEE COMMENTS FOR TIME DIMENSION SPROC…

 

USE[DanDev]

GO

/****** Object: StoredProcedure [dbo].[PopulateDimDate] Script Date: 05/19/2010 17:13:24 ******/

SETANSI_NULLSON

GO

SETQUOTED_IDENTIFIERON

GO

CREATEprocedure [dbo].[PopulateDimDate] (@start asdatetime, @end asdatetime)

as

declare @dte asdatetime

— run the below statement to test

— exec PopulateDimDate ’01 Jan 2000′, ’31 Dec 2049′

— 150 years worth takes 22 seconds to create 54,787 rows

— either create or truncate the table

ifobject_id(‘DimDate’)isnull

begin

CREATE TABLE [dbo].[DimDate]

(

[DateKey] [int] NULL,

[CalDate] [datetime] NULL,

[TextDateShort] [nvarchar](6) NULL,

[TextDateLong] [nvarchar](11) NULL,

[CalWeek] [int] NULL,

[CalMonthNum] [int] NULL,

[CalQtr] [int] NULL,

[CalYear] [int] NULL,

[IsWeekend] [bit] NULL,

[DoW] [nvarchar](9) NULL,

[DoM] [int] NULL,

[CalMonthName] [nvarchar](9) NULL,

[IsFirstDayOfMonth] [bit] NULL,

[IsLastDayOfMonth] [bit] NULL,

[IsLeapYear] [bit] NULL

)

end

else

truncate table dbo.DimDate

— use while to create all rows with just date field populated to keep it quick as poss

set @dte = @start

while @dte <= @end

begin

insert into DimDate (CalDate) values (@dte)

set @dte = dateadd(dd,1,@dte)

end

— Now populate all additional fields

update DimDate

set DateKey = year(CalDate)*10000+month(CalDate)*100+day(CalDate),

TextDateShort = left(convert(varchar(16),CalDate,113),6),

TextDateLong = left(convert(varchar(16),CalDate,113),11),

CalWeek = datepart(week,CalDate),

CalMonthNum = month(CalDate),

CalQtr = (month(CalDate)/4)+1,

CalYear = year(CalDate),

IsWeekend = case when datename(dw,CalDate) in(‘Saturday’,‘Sunday’) then ‘TRUE’ else ‘FALSE’ end,

DoW = datename(dw,CalDate),

DoM = day(CalDate),

CalMonthName = datename(month,CalDate),

IsFirstDayOfMonth = case when day(CalDate) = 1 then ‘TRUE’ else ‘FALSE’ end,

IsLastDayOfMonth = case when day(dateadd(dd,1,CalDate)) = 1 then ‘TRUE’ else ‘FALSE’ end,

IsLeapYear = case when year(CalDate) % 4 = 0

and (year(CalDate) % 100 <> 0

or year(CalDate) % 400 = 0) then ‘TRUE’ else ‘FALSE’ end

Filed under Work

Using SSIS to do live realtime currency conversions

April 29, 2010 Leave a comment

Saw this today on http://www.sqlservercentral.com and thought it would come in handy one day…
 

Filed under Work

Internet Explorer Enhanced Security vs SharePoint Central Admin

April 14, 2010 Leave a comment

A colleague came across an issue today – When trying to create a web application in Central Admin, filled in all the details correctly then clicked OK and nothing happened, the page didnt start loading or anything.
 
We figured out it was because Internet Explorer enhanced security was enabled, one way to fix this would be to switch it off but that’s not the best solution.  The other way is to ad the central admin URL to the list of Intranet Zone URLs in Tools>Options>Security
 
Did that, closed and opened internet explorer and now it works Open-mouthed
 
 

Filed under Work

SharePoint 2010 Beta

March 23, 2010 4 Comments

Having a go at installing and configuring SharePoint 2010 Beta and Office 2010 Beta, I just know there will be bits I’ll want to remember for when I do this live so here are the bits…
 
  • Installing on a VM with Windows 2008 R2 x64
  • Logged on as a specific account that I created just for SharePoint Installation.
  • Ran ‘OfficeServer’ installer
  • Installed prerequisites
  • Installed SharePoint
  • SharePoint configuration failed with the ‘allowInsecureTransport’ error on the create sample data pass of the configuration wizard
  • Googled it and found my way to this hotfix http://support.microsoft.com/kb/976462 (thanks to http://iancampbell.blogspot.com/2009/11/sharepoint-2010-allowinsecuretransport.html)
  • Installed the hotfix, restarted the server
  • Reran SharePoint config wizard – Success!
  • SharePoint popped up in internet explorer with ‘Template Selection’ (didnt do anything with this for now)
  • Opened Central Admin (ooh looks different! Smile) Usually I would create Shared Service Provider, web apps, site collection etc… but I’m curious that it took me straight to template selection so gonna give that a go see what happens (maybe it will do it all for me Open-mouthed.
  • Went back to ‘Template Selection’, chose ‘Business Intelligence Center’ (sounds interesting!) under enterprise
  • Clicked ok and it’s been ‘Processing…’ for quite a while, guess it’s got lots to do.
  • Now I get a screen saying ‘Set Up Groups for this Site’ – Seems like it is going to walk me through this.
  • Accepted defaults and clicked OK
  • Now I’m on the Home screen, starting to wonder about reporting services, a quick Google gets me here http://blogs.pointbridge.com/Blogs/2010wave/Pages/Post.aspx?_ID=18
  • He raises a good point that we need SQL Server 2008 R2, luckily we already installed this as we’re trying all the latest versions of stuff on this server.
  • It seems there is no reporting services add-in required here just central admin configuration (fantastic!) <– NOT TRUE (doh!)
  • Hmm, my ‘General Application Settings’ list is different to his screenshot – Specifically ‘Reporting Services’ is missing, back to Google…
  • Ok turns out the prev blog is misleading – There is actually a reporting services add-in, it’s here http://www.microsoft.com/downloads/details.aspx?FamilyID=16bb10f9-3acc-4551-bacc-bdd266da1d45&displaylang=en
  • Log off the sharepoint installer account and log back in as administrator
  • Before I download this I’m going to go back to Reporting Services config and change to integrated mode (was prev set up as native)
  •  NOTE – When I changed the database to one using integrated mode it didint immediately reflect that in the ‘Report Server Mode’ – It still showed Native.  Restarted the reporting services config tool and it now shows Integrated mode.
  • Now that’s done I’m downloading and installing the reporting services add-in
  • There is only one (ie no 32bit 64bit choice) presume it will be ok
  • Ran straight from msi (i.e. no cmd SKIPCA… blah blah)
  • Installer closed when it finished so missed whether it said it succeeded or not – opening central admin to check
  • Aha! I’ve now go the Reporting Services section in ‘General Application Settings’
  • First up – ‘Reporting Services Integration’
  • There’s a new feature here – It lets you activate reporting services feature on all or specific site collections, you used to go into site collection features to do this.
  • Activate site collection feature failed so I’ve gone off to try and do it manually, feature seems to have a new name (unless Im going wrong which is highly possible!) I can only see a feature called ‘Report Server File Sync)  bit more googling required I think!
  • Its because Im not at the site collection level (just the site level) so dont have ‘Site Collection Features’ list
  • Tried to navigate to high level site collection but administrator didnt have access, signed in as sharepoint installer account instead which worked
  • Site Actions -> Site Settings -> Site Collection Administration -> Site Collection Features
  • Activated ‘Report Server Integration Feature’
  • Now back to central admin to finish Reporting Services configuration
  • ‘Add a report server to the integration’ – my server name was already there as this is a single server install, clicked ok and entered administrator credentials
  • ‘Set Server Defaults’ – not expecting to change anything here but like to look anyway as its a good indicator of whether everything is playing nicely together
  • Right, I think all thats left is to add the report server content types to the doc libraries.
  • Actually, scratch that – Just remembered I’ve created this ‘Business Intelligence Center’ web site but what I want is a ‘Report Centre’ or at least thats what it used to be, trying to create a sub site using ‘Report Centre’ template which should have come with the reporting services addin.
  • Ok Report Centre isnt there – Just ‘Business Intelligence Center’ which I’ve already created, another trip to Google to find out the deal here…
  • Thanks to this blog http://www.toddbaginski.com/blog/archive/2009/11/20/which-sharepoint-2010-site-template-is-right-for-me.aspx I found out Report Center has been deprecated – So I’ll carry on with my ‘Business Intelligence Center’ site.
  • All Site Content -> Create -> Asset Library (It appears Report Library is deprecated – http://business-intelligence.kdejonge.net/installing-ssrs-2008-r2-on-sharepoint-2010)
  • I’ve named it Report Library (will probably confuse me later!)
  • Hey now I have the ribbon like in Office 2007, clicked Library settings then ‘add from existing site content types’ added the report server content types.
  • Went into Data Connections, Library Settings (as above) added the report data source content type
  • Now it’s ready for uploading reports

Stopping this blog here – Might do another one on new features once I’ve played with it a bit.

 

Filed under Work

SharePoint Reporting Services Integration

March 12, 2010 Leave a comment

Yet again I find myself trying to debug a SharePoint Reporting Services Integration and each time I go searching the internet for the same blogs and guides to help me.  So I thought it would be a great idea to post all the links here then I can refer back to them and save myself some Googling time!
 
Fantastic overview by Jesper M. Christensen on how Kerberos works in a SharePoint/Reporting Services Integration
 
Troubleshooting articles from the same author as above
 
A blog entry by Martin Kearn detailing the steps required to enable Kerberos in a MOSS 2007 environment
 
Microsoft guide to Configuring Reporting Services for SharePoint 3.0 Integration
 
Getting it to all play nicely together can be a real pain but it can and does work, one of the most painful things can be getting the right SPNs set up – For this I would recommend the DelegConfig tool created by brian-murphy-booth, make sure you get the right one depending on whether you have IIS 6 or 7.
 
Make sure you add the DelegConfig web application to the same website in IIS that is hosting the front end you are trying to debug (i.e. SharePoint).
 
 

Filed under Work

Newer posts →