Week of Business Intelligence with Pragmatic Works Webinars

This week Pragmatic Works put togther a "Week of Business Intelligence" webinars. It was a lot of material to cover in such a short amount of time but each session is a must. If you didn't get a chance to view them make sure to check out the recorded sessions at the below link. Most of the webinars have code samples you can download.

Some of my favorite:

1. Designing a Data Warehouse
   
2. Loading a Data Warehouse with SQL Server Integration Services (SSIS)
   
3. Report Design Recipes for BI Solutions

http://www.pragmaticworks.com/resources/webinars/March2010Webinar.aspx

SSAS 2008 Time Calculation Dimension (Calculated Members)

Over the past couple of weeks I have been working on setting up a PPS 2007 dashboard and came across a Time Calculation Dimensions in SSAS 2008. At first I didn't truly understand the true purpose or functionality but after time I gained respect as I created new Time Calculation Calculated Members. This article will explain the steps needed to setup a Time Calculation dimension and add multiple Calculated Members that are stored in the Time Dimension.

1. Go to your dsv of the cube and add a new Named Calculation field to your time dimension, Make sure to call it PeriodID and the expression should be " N'Current Period' " (make sure to drop the double quotes
   
2. Create a new Dimension with the Dimension Wizard
3. Select use an existing table
4. tap into your current time dimension, in my case I am using DimWeek
5. Make sure to have the primary key of the dimension selected and no other fields
6. Name the new dimension "Time Calculation"
7. Now that the dimension is created bring it up
8. Drag over the PeriodID (the Name Calculation you created on step 1) to the Attributes section of the Dimension Structure tab
9. Rename the field to Time Calculation and you should have something like this
10. Now open the cube up and go to the Calculation
11. Create a new Calculated Member
12. Name: [Prior Year]
13. Parent hierarchy: Time Calculation. Time Calculation
14. Expression: (parallelperiod([Week].[FiscalCalendar].[Fiscal Year], 1, [Week].[FiscalCalendar].CurrentMember), [Time Calculation].[Time Calculation].&[Current Period])
15. 
16. Now you need to setup your Dimension Usage
17. Setup the Measure Group, Referenced, Intermediate Dimension (Week in this case), Reference Dimension attribute (Time Calculation), Intermediate Dimension attribute (PeriodID)
18. 
19. Build and deploy cube
20. Now in your cube browser you will see a new dimension call Time Calculation
21. Add the new dimension and drag a metric and the time dimension and you will see the power of the Time Calculation Dimension

Here is a list of some other Time Calculations:
CREATE MEMBER CURRENTCUBE.[Time Calculation].[Time Calculation].[Prior Year]
AS (parallelperiod([Week].[FiscalCalendar].[Fiscal Year], 1, [Week].[FiscalCalendar].CurrentMember), [Time Calculation].[Time Calculation].&[Current Period]),
VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[Time Calculation].[Time Calculation].[Year to Date]
AS aggregate(crossjoin([Time Calculation].[Time Calculation].&[Current Period], PeriodsToDate([Week].[FiscalCalendar].[Fiscal Year], [Week].[FiscalCalendar].CurrentMember))),
VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[Time Calculation].[Time Calculation].[Year to Year Change]
AS [Time Calculation].[Current Period] - [Time Calculation].[Prior Year],
VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[Time Calculation].[Time Calculation].[Year to Year Change %]
AS Case
When ISEMPTY([Time Calculation].[Prior Year]) Or [Time Calculation].[Prior Year] = 0
Then IIF([Time Calculation].[Current Period] > 0, 1, 0)
Else
[Time Calculation].[Year to Year Change] / [Time Calculation].[Prior Year]
End,
FORMAT_STRING = "Percent",
VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[Time Calculation].[Time Calculation].[Prior 3 Periods]
AS Aggregate({[Week].[FiscalCalendar].CurrentMember.Lag(3) :
[Week].[FiscalCalendar].CurrentMember.Lag(1)}, [Time Calculation].[Current Period]),
VISIBLE = 1 ;

CREATE MEMBER CURRENTCUBE.[Time Calculation].[Time Calculation].[Prior 6 Periods]
AS Aggregate({[Week].[FiscalCalendar].CurrentMember.Lag(7) :
[Week].[FiscalCalendar].CurrentMember.Lag(1)}, [Time Calculation].[Current Period]),
VISIBLE = 1 ;

CREATE MEMBER CURRENTCUBE.[Time Calculation].[Time Calculation].[Year to Date %]
AS [Time Calculation].[Time Calculation].&[Current Period] /[Time Calculation].[Time Calculation].[Year to Date],
FORMAT_STRING = "Percent",
VISIBLE = 1;

Cross Site List RSS Feed with SharePoint DataView (No need for Anonymous Access)

1. Lets start out by pulling up SharePoint Desinger 2007


2. Open up your site (in my case I am using MySite)


3. On the Data Source Library tab on the right portion of the designer
   


4. Connect another library in the lower right
   


5. Now that you have created a connection to another library you need to "Refresh library" to get the objects to appear
   


6. Now your Data Source Library should look like this (image)


7. We now move on to the list you are trying to hook into. On the Date Source Library select "Connect to a script or RSS feed.."


8. On the General tab give it a name, in my case "BI Calendar"


9. On the Source tab the default values are good


10. Just add the URL for the List RSS feed you want to view
11. Go to the list, select the action, select "View RSS Feed"
    


12. Take the URL and drop it into the "Enter the URL to a server-side script" field
    
13. At this
    point if you a specific view that you want to reference you are going to need to
    add the GUID to the view in the above step as well. This was difficult step to figure
    out and I hope I can help save someone else the time I spent on it. Open up the
    view, modify the view and pull out the "&View=" from the URL. This parameter will be used in the server-side script connection to the List RSS Feed. The next step is to add a parameter to the DataView properties. A URL with out the View will look like this: "http://MyServer/_layouts/listfeed.aspx?List=a4a88bd0%2Ddf29%2D4f54%2Da924%2D195be47346ed" and a URL with the View will look like this: "http://MyServer/_layouts/listfeed.aspx?List=a4a88bd0%2Ddf29%2D4f54%2Da924%2D195be47346ed&View=%7B9F3ACEAB%2D75DF%2D4C04%2D83C2%2D3A5733A6CA31%7D"
14. 
    Go to the the Login tab, in my case we are using the Kerberos so "Use Windows Auth"
    works, not sure what other cases this will work. You may have to play this depending
    on the type of Auth you have setup on your farm. Keep in mind if you enter your
    creds they will be stored in plain text. This is the point in which you get around
    the Anonymous Access part of a List RSS Web Part .


15. Now we need to add a SharePoint DateView to the page. Insert, SharePoint Controls,DateView
    


16. The next step is to link the Server-Side Scripts to the SharePoint DataView web part we just
    added. Right click on the server side scrip you just created in the Data Source
    Library and select "Show Data". This will allow you to do two things, validate the
    the connection is working correctly and view the list of RSS fields that come over
    in the feed
    


17. Now scroll down on the Data Source Details tab to the "Item" section. Then select "title &
    pubDate" by holding down your CRTL key.
18. Then select the "Insert Selected Fields
    as.." drop down at the top of the Date Source Details tab
    
    This step binds the fields from the RSS feed to the DataView



19. Go to the DataView and right click edit properties. We want to edit the title and
    convert it into a hyper link.
    
    Make sure the "Text to display" is {title} and the "Address" is {link}.
    


20. Next I clean up the headers on the webpart and the columns
21. Finally save and deploy.
    You may first see an error page but just select the "refresh page" link and you
    are done (image)

SharePoint Data View Web Part

Here is a simple step by step on how to setup a DataView web part in SharePoint Designer. This is a quick and easy way to use current datasources and integrate them into a current page. Link to pdf

Link to article

Deploying a Business Intelligence Solution Using SharePoint, Reporting Services, and PerformancePoint Monitoring Server with Kerberos

Over the past month I have taken part in a deployment of MOSS 2007, Performance Point Server 2007, SQL Server Reporting Services 2008 on Windows Server 2008 with Kerberos authentication. This is a pretty complex configuration and I found an ideal resource by Carl Rabeler that I wanted to share. The article covers the design requirements for this business intelligence solution, its logical architecture, the challenges we faced in architecting and implementing this solution, and our resolutions to these challenges.

Make sure to check out the original article from sqlcal.com: Link