Install Sharepoint 2010 Web Front End (WFE) on a Report Server to Join Existing Farm

A short guide showing the steps involved in installing the Sharepoint Web Front End on a Report Server (i.e. your Business Intelligence server).

First you will need to install the Sharepoint 2010 SP1 Prerequisites.   If this is a SQL Server 2012 server, of course you would be installing these prerequisites prior to installing any SQL Server 2012 components.

  1. Install the Web Front End on the Report Server and join the existing farm (steps outlined here http://msdn.microsoft.com/en-us/library/aa905869(v=sql.105).aspx
  2. Finally you will perform the integration between SQL Server and Sharepoint, the approach of which differs slightly between SQL Server 2012 and earlier releases.

Enjoy ! and don’t forget your rollback plan ! 🙂

 

 

 

How to Remove Carriage Return Line Feed Tab From a String in TSQL

Thanks David for this post http://metadeveloper.blogspot.com.au/2006/03/remove-carriage-return-line-feed-tab.html on how to remove carriage return, line feed and tab from a string in TSQL.   The snippet below will substitute these characters with a space and grab only the first 100 characters.

LEFT(REPLACE(REPLACE(REPLACE([YOUR_STRING], CHAR(10), ' '), CHAR(13), ' '), CHAR(9), ' '), 100)

Sharepoint B.I. Integration – Should I Install Everything on One Box ?

That is a terrific question.  But first when we talk about Sharepoint integration, we are talking about Reporting Services and Powerpivot.

Both Reporting Services and Powerpivot are implemented as distributed applications with components that run on the Web Front End (WFE) and Application tiers, whilst application data is stored in the Data Tier.  These components in the integrated B.I. stack tie into the three tiers as illustrated below :

Image

There are different ways the WFE, Application and Data Tiers can be deployed.  Installing everything on the one server offers the simplest deployment and simplest licensing model however you are limited to the processing power of that single server.   All other deployments are distributed across two or more servers, which means you have some flexibility to build capacity around where you need it : either the WFE, the application tier or the data tier.

So to answer the question “Should I Install Everything on One Box?” you need to do some capacity planning around each of these tiers.    It might also pay to plan to put some parameters around the software and hardware budget as the 2 and 3+ tier deployment have licensing implications.

Likewise, these latter deployments require careful planning around security – so if your organisation doesn’t want to play Kerberos, this could be a showstopper, or at least an incentive to deploy everything on a Single Server.

The different deployment modes are outlined as follows :

1. Single Server (1-Tier)

Everything is installed on the same box – nice and easy.  All other deployments will have important licensing considerations as well as careful planning around security (Kerberos and Claims Authentication).

2. Two Servers (2-Tier)

WFE and Application Tier on Server 1 and Data Tier on Server 2

3. Three Servers (3-Tier)

Application Tier on Server 1 and Data Tier on Server 2 and WFE on Server 3

4. More than Three Servers (3-Tier Scaleout)

Application Tier on Server 1 and Data Tier on Server 2 and multiple WFE’s on Servers 3+1

Enjoy more about deployment topologies here Reference – http://msdn.microsoft.com/en-us/library/hh231674.aspx

SSRS – Toggle Visibility Show Initially as Expanded + –

‎Reporting Services provides some nice options to allow user’s to feel like they are interacting with the data, by expanding lower levels of detail when they click on the plus (+) sign in the report’s tablix. You can also set the initial Toggle state of report items as expanded i.e. (-) sign.  There is a catch though in that when you Show your initial state as expanded, the items may expand correctly but the toggle sign appears reversed (i.e. +  instead of -).

Nice solution to the problem demonstrated Here.

Save Valuable Hours with Analysis Services Deployment Utility Command Line

The Analysis Services Command Line Utility is one of the 5 methods of deploying Analysis Services databases.

ichi, ni, san, yon, go …

http://msdn.microsoft.com/en-us/library/ms175446(v=sql.105).aspx

The deployment wizard is a brilliant piece of work taking the headache out of cube deployments.   However the Next, Next, Next does get tiresome, you will reap the rewards in time and mouse clicks by dishing out some prepared command lines.

A few suggestions on just one approach – the deployment utility …

1. Utilise your project configurations to easily switch deployment settings between environments …

http://www.artisconsulting.com/blogs/greggalloway/Lists/Posts/Post.aspx?ID=14

2. Set your cube’s Data Source and Build the solution.  There is a command line version of a “Build” here …

http://dataonwheels.wordpress.com/2011/06/01/simple-batch-script-to-generate-xmla-and-deploy-ssas-db/

3. Deploy the build with the currently saved data source and project deployment settings and create a log …


<DriveLetter>:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Microsoft.AnalysisServices.Deployment.exe
<DriveLetter>:\Builds\YourSSASFolder\bin
/s: <DriveLetter>:\Builds\YourSSASFolder\your_deployment.log

SSAS – The Measures Cube Either Does Not Exist or Has Not Been Processed Error

You attempt to deploy calculation changes to your Analysis Services cube and get the error “The Measures Cube Either Does Not Exist or Has Not Been Processed”.

It is likely one or more of your MDX Create Member constructs are missing CURRENTCUBE i.e.


CREATE MEMBER CURRENTCUBE.Measures.[Some New Measure] AS
 SUM([All Things Red],[Measures].[$Amount]),
FORMAT_STRING = "Currency",
ASSOCIATED_MEASURE_GROUP = 'Some Measure Group',
VISIBLE = 1;

Analysis Services 2008 R2 – Unable to Apply Filter – Internal Error An Unexpected Exception Occurred

You are running Analysis Services 2008 R2 and have created some calculated measures involving a dynamic set.  When you browse the cube and add a second filter to these measures, you get the error “Unable to Apply Filter – Internal Error: An unexpected exception occurred”.   It is likely you are still on the RTM release of Analysis Services 2008 R2.   You could look at applying a fix for this issue here …

http://support.microsoft.com/kb/981899

or check out the latest service pack for this edition https://popbi.wordpress.com/2012/08/02/what-is-the-latest-service-pack-or-cumulative-update-for-sql-server/.