Add a Default to an Existing Table T-SQL

A quick implementation for adding a default value to an existing column in a table …the following example adds a default constraint “YourDefaultConstraintName” of 0 (zero) to the column “YourColumn” in the table “YourTable” …


ALTER TABLE YourTable
ADD  CONSTRAINT YourDefaultConstraintName
DEFAULT (0) FOR [YourColumn]

 

If the constraint already exists you will need to drop it first then add the constraint with the same name but with the new default value…Drop constraint sample code here …


ALTER TABLE YourTable DROP CONSTRAINT YourDefaultConstraintName

Powerpivot Data Refresh Toolkit

You have deployed your PowerPivot workbook to Sharepoint and are experiencing Data refresh issues.

Before you go chasing your tail, look no further than the following 3 links that discuss what Powerpivot Data Refresh is all about…

1. PowerPivot Data Refresh Whitepaper

http://msdn.microsoft.com/en-us/library/ff976569.aspx

2. PowerPivot Data Refresh Account Checklist

http://technet.microsoft.com/en-us/library/ff773327.aspx

3. Troubleshooting PowerPivot Data Refresh

http://social.technet.microsoft.com/wiki/contents/articles/3870.troubleshooting-powerpivot-data-refresh.aspx#UEA

Yes this is a lot of info to wade through, however at the end you will hopefully be wiser and solved your data refresh issue.

Dynamic DTSX Generator with BIDS Helper

BIML (Business Intelligence Markup Language) – SSIS is the plumbing component of the Business Intelligence industry so whether you love getting your hands in dirty trenches or not, automatic package generation is a lucky dip for all folk.

– > You can read about it and download from here…

http://bidshelper.codeplex.com/wikipage?title=Biml%20Package%20Generator

-> Cool walkthrough here from John Welch…

http://agilebi.com/jwelch/2011/05/13/biml-functionality-in-bids-helper/

-> Support can be found in the Codeplex BIDS Helper tool

-> Makes it easy to generate packages dynamically

-> Can save you bucketloads of time in Data Migration or Data Warehousing projects

I must admit I thought the name sounded stoopid first time I heard it, but its catchy….Biml ..Bim-l …Bimmel …Beeemil ……

New Performance Point 2013 Features

Performance Point 2013 is just around the corner and you can take a peak of the new features here in this great post by Kevin Donovan http://blogs.msdn.com/b/performancepoint/archive/2012/08/03/what-s-new-in-performancepoint-services-2013.aspx.

First whats not cool ?

The charts and grids still may not address concerns for a more visually exciting and customisable set of charts and grids – yawn.  Also the support for new Sharepoint themes is welcoming however how easy it is for general users to quickly design and deploy spicy mobile ready dashboards remains to be seen.

Whats cool ???

1. Themes to spice up those Dashboards.

2. The BI Center now gets a different skin, although a more metro interface would be good.

3. The Dashboard Designer is now easier to launch.

4. A host of Filter enhancements and performance improvements such as being able to filter a measure.

5. Easier support for sites that don’t do Kerberos, and an improved migration tool.

Thats it for now, I’ll be putting it through its paces in the coming months.  All in all, I think its worth the upgrade simply for the filter on a measure feature and improvements on filters in general.  Still not glossy magazine dashboard material but an improvement on a proven and easy to use Performance Point platform.

Pass Summit 2012 – BI Session Snap !

With just over a week now until Pass Summit 2012, its time to review some of the cool sessions to go to in the Business Intelligence track ! 

Around the World with the SharePoint BI Toolbelt (BID-212-S)
BI Information Delivery
Brian Knight (Pragmatic Works)

Dashboard Design: Making Reports Pop (BID-305)
BI Information Delivery
Paul Turley (SolidQ)

Building Self-Service BI Apps Using PowerPivot and Excel (BID-205-M)
BI Information Delivery
Diego Oppenheimer (Microsoft)

BI Architecture with SQL Server 2012 and SharePoint 2010 (BIA-318)
BI Platform Architecture, Development & Administration
Rod Colledge (StrataDB)

Dashboards: When to Choose Which MSBI Tool (BID-206)
BI Information Delivery
Melissa Coates (Intellinet)

Mobile BI with SSRS and SharePoint (BID-200)
BI Information Delivery
Angel Abundez (DesignMind)

Mobile Business Intelligence (BID-302-M)
BI Information Delivery
Jen Underwood (Microsoft)
Carolyn Chau (Microsoft)
John Sirmon (Microsoft)

Mobile Business Intelligence for Everyone, Now! (BID-102)
BI Information Delivery
Jen Stirrup (Copper Blue Business Intelligence)

Zero to BI Semantic Model with SQL Server 2012 (BID-399-P)
BI Information Delivery
Peter Myers (Bitwise Solutions)

10 Extraordinary Things to Achieve with Integration Services 2012 (BIA-322-S)
BI Platform Architecture, Development & Administration
Peter Myers (Bitwise Solutions)
Matthew Roche (Microsoft Corporation)

Configuring Kerberos for SharePoint 2010 BI in 7 Steps (BIA-304-M)
BI Platform Architecture, Development & Administration
Chuck Heinzelman (Microsoft)

 

Set a Reporting Services Parameter default to Today or Last Month

To format a Reporting Services parameter with the current date, Go to the Parameter Properties, Select Default, and click the fx button for Specify Values.  Enter the following expression to Format a Date Value in the format yyyyMMdd e.g. 23rd October 2010 as 20121023.


=Format(Today(),"yyyyMMdd")

Likewise to just get one month previous to current date in the same format


=Format(DateAdd("m",-1,Today()),"yyyyMMdd")

Performance Point Cascading Parameters Connection Formula Example

A simple but intuitive requirement in a report is the ability to restrict the 2nd parameter values based on what you selected in the 1st parameter.   Such an example might be to restrict the list of Staff depending on the Office you selected previous.

In Performance Point this is known as Cascading parameters and was only ushered in with Sharepoint 2010 Service Pack 1.

The implementation of this requires you to insert the following code in the Connection Formula when creating the connection between the 1st filter and the 2nd filter within the Performance Point dashboard.  When creating the filter you could either select “MDX Query” or “Member selection”.

Last but not least you need to create a connection between the 1st parameter and the 2nd parameter inside your Performance Point dashboard.  Drag in the filters as you normally would, then create a connection from the 1st filter to the 2nd filter.  The connection from the 1st to the 2nd filter has a Connection formula defined along the lines of the following.  Note <<SourceValue>> is a moniker and needs to be typed as is.  If your 1st filter and 2nd filter come from different Analysis Services dimensions, then the Measure Group is used to determine the list of member’s in the 2nd filter that relate to the selection made in the 1st filter.


NONEMPTY(EXISTS({[Dimension you want to restrict].children},&lt;&lt;SourceValue&gt;&gt;,'Your Measure Group'))