So you’ve got SQL Server 2012 SSRS integrated with SharePoint 2010, and an Analysis Services Tabular model deployed. So how do you go about creating reports with the new Power View tool?

This walkthrough will take you through the steps, so let’s get Started.

First of all we’ll need to create a .BISM (or Business Intelligence Semantic Model) file in one of our SharePoint libraries that points to our SSAS Tabular model. In this example I’m going to use a PowerPivot library that I’ve already got hanging around, but before we can create the .BISM file we need to add the appropriate content type to our library. We’ll do so by opening our library and clicking on the library tab at the top of the screen.

From there we’ll want to look to the far right in the “Settings” group and click on “Library Settings”.

In our Library settings screen click on “Advanced settings” under the “General Settings” group.

Our objective here is to switch “Allow management of content types?” from “No” to “Yes”. Make it so, as shown below and then click OK. Once you click OK you should be directed back to the main Library settings screen.

Now that we’re able to manage our Content Types, we’ll need to add the BISM content type to our library. From our current screen we’ll need to click on “Add from existing site content types” as shown below.

We want to add the “BI Semantic Model Connection”. Do so by locating it as shown below, highlighting it, clicking add, and then click OK at the bottom. That should be it, our new content type should be ready to rock and roll.

Now we need to actually create our BISM file. Navigate back to your library, then click on the “Documents” tab. Under the “New Documents” dropdown, you should now see our new “BI Semantic Model Connection” content type. Let’s go ahead and create a new document using this content type.

This screen should be pretty straightforward, simply name your connection and then provide the server name and database name of where your tabular model resides, then click OK.

And that’s it! You should now see something similar to the following in your library. As a final test, we can throw together a quick Power View report using the icon shown below at the top far right.

With very little effort we can throw together a quick report in Power View just to prove that everything is working correctly.

I just recently got my SharePoint 2010/SQL Server 2012 personal development environment set up, so I haven’t spent much time with Power View yet, however I can say I really like what I’m seeing so far. It’s really easy to use, visually appealing, and highly interactive. I REALLY wish we could hit SSAS multidimensional data sources directly though….Although I’ve heard that functionality may be coming in the future.  I think that probably wraps it up for this post. Hope this helps.

A few months back I logged into a machine that I had been assured SSMS had been installed on, but I couldn’t find the shortcut anywhere. I figured that more than likely the shortcut didn’t get pushed out to all the profiles, but if I could dig up where the executable was, I could just create my own shortcut. Sure enough with a little digging I found the .exe here:

C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe

This was for SQL Server 2008R2, but I imagine other versions are not far off the same path.

From there it’s just a matter of creating a shortcut, and since this post needs some pictures, let’s go ahead and show how that’s done.

Just right click on your desktop and go to New Shortcut

Then slap in the path to ssms.exe

And finally name that guy something you’ll remember

And that’s all there is to it, we have our SSMS Shortcut

Hope this helps.

Although the circumstances are somewhat rare, there are times where you’ll want to have your analysis services cube handle a many to many relationship. In this walkthrough we’ll take a look modeling a M2M dimension using a bridge table that hangs directly off our fact. Before we get started I would like to mention that modeling M2M relationships in your DW or CUBE can have a serious negative impact on performance, particularly so if your bridge table is connected directly to your fact and your fact table is large. That said, this is an exercise more in how, not when, why, etc. The same concept can be applied between two dimensions with far less of a performance impact. Now that we’ve got the disclaimer out of the way, let’s set up a scenario (I’ll just be making this up as I go) and see how we can implement a M2M relationship in our cube.

The scenario: let’s pretend we have a very small star schema db that contains the following dimensions:

1. DimAuthor

2. DimBook

And the following facts:

1. FactBookSales

Now we know that books quite often have multiple authors and we need to be able to show that in our cube. However the relationship between DimAuthor and FactBookSales in a typical star schema would be 1 to many (1 author can have many books). In order to properly express the fact that a book can also have many authors we need to create a bridge table to keep track of which books and authors belong together, just like we would in an OLTP model. We’ll call this bridge table DimAuthor_x_FactBookSales. The final ERD would look something like this: (bridge table highlighted)

Now that we’ve seen what our data model looks like, let’s take a look at what data we’re dealing with. I’ve kept the volume of data REALLY low as I’m making this up as I go, and I think it’ll be easier to understand with less data. Each table (with the exclusion of the bridge table has only 3 values in it, so if I run a select * statement on each table we’ll get the following values.

Looking at the values in DimAuthor and DimBook, we clearly see there are 3 authors and 3 books. If we direct our attention to FactBookSales we can see that each book has been sold 1 time. DimAuthor_x_FactBookSales is where things start to get interesting, and where we’re storing which books and authors belong together. If you look closely at that table you should notice the following:

1. Tavis Lovell has been an author on every book, and is the only author on “A guide to gnarly guitar solos”

2. Tony Maddin has only co-authored 1 book: “SharePoint Nerds Unite”

3. Jonathan Mast co-authored 2 books : “SharePoint Nerds Unite” and “how to ruinate your friends at videogames”

Keeping the above in mind, let’s move onto cubing our data in analysis services. I’ve already created an SSAS project in BIDS and added the appropriate data source and data source view. In order to create our cube we’re going to use the wizard, by right clicking on the cube folder and selecting “new cube” as shown below.

On the first two screens of the wizard we’re just going to go with the default option of creating the cube using existing tables and click next until we arrive at the 3^rd screen which asks us to select our measure group tables. In addition to selecting our fact table as a measure, we must also select our bridge table as a measure in order for our M2M dimension relationship to work. This can be seen in the screenshot below:

In the remaining screens we’ll accept the default options each and simply click next. The final review screen of the wizard should look like this:

If we were to deploy and process our cube at this point, and then browse the cube by Author and Book we’d see the following results:

Note that it looks as though each Author is shown as related to each book. Based on our review of the data earlier, we know this isn’t correct. In order to get the correct results we’ll need to navigate to the dimensions tab of our cube as shown below.

We can clearly see that there is currently no relationship between Fact Book Sales and Dim Author (highlighted in yellow). In order to fix our results we’ll need to create a relationship by clicking on the highlighted field, in order to open the relationship screen as shown below.

In the “Select relationship type” dropdown we’ll want to select “Many-to-Many” and for our immediate measure group we’ll select our Bridge table “Dim Author x Fact Book Sales. Once that’s done, we can click ok and see our new relationship highlighted below.

It should be noted that by selecting “DimAuthor_x_FactBookSales” as a measure group table during the “new cube” wizard, it automatically created a referenced relationship between “Dim Book” and “Dim Author x FactBookSales” as seen above. If you’re adding your new m2m dimension and bridge table to an existing cube, you may need to create these reference relationships manually. Please reference the screenshot below for how the referenced relationship was created.

With our new many to many relationship in place we can now reprocess our cube. Once completed, we should now see the appropriate books for each author when browsing the cube, as shown below.

Note how the Totals and Grand Total correctly reflect that there are really only 3 books that have been sold. Well I think that does it for this walkthrough, hope this helps.

In my last walkthrough we talked about how you could pivot a columns into a comma delimited string using the COALESCE function. In this walkthrough we’re going to combine that bit of code with the T-SQL PIVOT function and a little bit of dynamic sql to achieve “dynamic columns”. In this example I’m using SQL Server 2008R2, but I think it should work on any version that includes the PIVOT function which I believe was introduced in SQL Server 2005 if I remember correctly. So let’s set up a scenario!

I have a table called “TimesheetHours” that has the following columns:

1. TimesheetDay (DateTime) – Contains the date the hours were worked.

2. Hours (Int) – Contains the amount of hours worked on that day.

Writing a query that retrieves the total hours for each day for a given date range is fairly simple and would look something like this:

--declare and set variables

DECLARE

@v_StartDate      DATETIME = '2011-11-01',

@v_EndDate        DATETIME = '2011-11-03'

 

--select results

SELECT

      th.TimesheetDay,

      SUM(th.[Hours]) 'Hours'

FROM

      TimesheetHours th

WHERE

      th.TimesheetDay BETWEEN @v_StartDate AND @v_EndDate

GROUP BY

      th.TimesheetDay

The result set for the above query would look like this:

Suppose we wanted to show the date as the column header AND have the number of columns increase or decrease based on the number of days included in the range so that our result set looks like this:

To pivot the data into this layout, we can use the T-SQL pivot function. The query to pivot our data into the format shown in the image above would look like this:

DECLARE

@v_StartDate      DATETIME = '2011-11-01',

@v_EndDate        DATETIME = '2011-11-03'

 

SELECT

      *

FROM

      (

      SELECT

            th.TimesheetDay,

            th.[Hours]

      FROM

            TimesheetHours th

      WHERE

            th.TimesheetDay BETWEEN @v_StartDate AND @v_EndDate

      ) src

PIVOT

(

      SUM(src.[Hours])

      FOR src.TimesheetDay IN ([2011/11/01],[2011/11/02],[2011/11/03])

) AS PivotedView

The query above is static in the number of columns it shows however. I’ve highlighted the section of the code that contains the column names in green above. There are a few things we still need to do in order to make our column dynamic

1. We need to put together a comma separated list of the columns/days we want to show, based on the values of @v_StartDate and @v_EndDate.

2. Construct the rest of the query using dynamic SQL so we can insert our list from step one into the body of the query.

In order to get a list of columns we want to show, we can use the COALESCE script we covered in my previous post. The code to do that would look like this:

DECLARE

@v_Columns        VARCHAR(MAX),

@v_StartDate      DATETIME = '2011-11-01',

@v_EndDate        DATETIME = '2011-11-03'

  

--pivot and delimit values

SELECT @v_Columns = COALESCE(@v_Columns,'[') +   convert(varchar, TimesheetDay, 111) + '],['

                              FROM   

                                    (SELECT DISTINCT TimesheetDay FROM TimesheetHours) th

                              WHERE

                                   th.TimesheetDay BETWEEN @v_StartDate AND @v_EndDate

 

--delete last two chars of string (the ending ',[')

SET @v_Columns = SUBSTRING(@v_Columns, 1, LEN(@v_Columns)-2)

 

--show value

SELECT @v_Columns

The value of @v_Columns would be ‘[2011/11/01],[2011/11/02],[2011/11/03]’ for the given values of @v_StartDate and @v_EndDate in the above query. Now all we really need to do is convert our original pivot query into dynamic SQL and concatenate the value(s) of @v_Columns in the appropriate spot. All together the full query looks like this:

DECLARE

@v_Columns        VARCHAR(MAX),

@v_StartDate      DATETIME = '2011-11-01',

@v_EndDate        DATETIME = '2011-11-05',

@v_Query          VARCHAR(MAX)

 

--pivot and delimit values

SELECT @v_Columns = COALESCE(@v_Columns,'[') +   convert(varchar, TimesheetDay, 111) + '],['

                              FROM   

                                    (SELECT DISTINCT TimesheetDay FROM TimesheetHours) th

                              WHERE

                                   th.TimesheetDay BETWEEN @v_StartDate AND @v_EndDate

 

--delete last two chars of string (the ending ',[')

SET @v_Columns = SUBSTRING(@v_Columns, 1, LEN(@v_Columns)-2)

 

--construct sql statement

SET @v_Query =

'SELECT

*

FROM

      (

      SELECT

            CONVERT(VARCHAR(50), th.TimesheetDay, 111) AS TimesheetDay,

            th.[Hours]

      FROM

            TimesheetHours th

      WHERE

            th.TimesheetDay BETWEEN ''' + CONVERT(VARCHAR(50), @v_StartDate, 111) + '''

            AND ''' + CONVERT(VARCHAR(50), @v_EndDate, 111) + '''

 

      ) src

PIVOT

(

      SUM(src.[Hours])

      FOR src.TimesheetDay IN (' + @v_Columns + ')

) AS pivotview'

 

--execute sql statement

EXEC(@v_Query)

 

Not exactly the easiest code to read, but such is the “beauty” of dynamic sql (let’s not even get started on the nightmare debugging or security implications of dynamic SQL.). We do however now have a query we can run for multiple date ranges, and it will produce dynamic columns.

As an example if we run the query with the following values:

@v_StartDate      DATETIME = '2011-11-01',

@v_EndDate        DATETIME = '2011-11-03',

Our results will look like:

But if we ran the query with a date range that includes 5 days like this

@v_StartDate      DATETIME = '2011-11-01',

@v_EndDate        DATETIME = '2011-11-05',

Our results will look like this:

Well I think that wraps it up for this session.  This was really more an exercise than anything else.  If you’re trying to show a dynamic number of columns in SSRS, you can simply use the matrix control to achieve the same results without all the dynamic SQL and pivoting.  That said, go forth and pivot.

Wow, I am WAY behind on getting things posted to this blog. I have no shortage of reasons why the blog got demoted a step or two on my list of priorities, but I’m going to try and get the posts rolling again starting with this one.

First and foremost, I presented at the SharePoint Cincy 2012 conference a month or so ago with Chris Murphy from Ascendum. Our session was “BI on a budget” and the turnout for the conference was great. We actually had to have extra seating brought into our session while we were speaking. Thanks to everyone who came, and for all the positive feedback. At the session I promised I’d post our slide deck on my website, so here it is:

Sorry it took so long….more posts on the way soon.

At some point you might need to turn multiple rows from a column into a delimited list.  I think I saw this done for the first time about 3 years ago, and I clearly remember having to do a double take at the code upon seeing it.  It’s been in my sql-toolbox since then, though admittedly it doesn’t see much action.  At any rate I thought I’d share, so let’s set up a scenario to walk through.  Let’s say we have a table called ‘Numbers’ that has a column named ‘ID’ that contains integers.  If we ran the following query:

SELECT * FROM Numbers

We would get the following results:

 

The table basically contains the values 1-6 with the value 5 appearing twice.  What we want to achieve is to get our values to appear as one delimited (comma or otherwise) string like so:

‘1, 2, 3, 4, 5, 6’

We can use the COALESCE function in combination with a variable to achieve the desired results using the code below,  just make sure to change the table and column name to suit your specific scenario.

--declare local variables

DECLARE

      @v_DelimitedString VARCHAR(MAX)

 

--pivot rows into delimited string

SELECT

      @v_DelimitedString = COALESCE(@v_DelimitedString, '') + CONVERT(VARCHAR(10), ID) + ','

FROM

      Numbers

     

--trim off last comma

SET @v_DelimitedString = (SELECT SUBSTRING(@v_DelimitedString, 1, LEN(@v_DelimitedString)-1))

 

--show results   

SELECT @v_DelimitedString

Running this code against our Numbers table would produce the following results.

 

That’s almost what we’re looking for, although we’re seeing the value ‘5’ repeated multiple times since it appears multiple times in our data.  This problem is easy enough to solve though, by replacing our ‘Numbers’ table with a dynamic SELECT statement like so (see green highlight for changed code below)

--declare local variables

DECLARE

      @v_DelimitedString VARCHAR(MAX)

 

--pivot rows into delimited string

SELECT

      @v_DelimitedString = COALESCE(@v_DelimitedString, '') + CONVERT(VARCHAR(10), ID) + ','

FROM

      (SELECT DISTINCT ID FROM Numbers) Nbrs

     

--trim off last comma

SET @v_DelimitedString = (SELECT SUBSTRING(@v_DelimitedString, 1, LEN(@v_DelimitedString)-1))

 

--show results   

SELECT @v_DelimitedString

Now our repeating 5 value has been eliminated and our results are being returned as so:

It’s a pretty nifty trick that was a little more useful before table value parameters were available.  It can still come in handy if you wanted to do something like use the T-SQL PIVOT function to return a dynamic set of columns.  I’ll be covering how to do that in my next post.  Hope this helps.

 

I recently took and passed the MCITP: Business Intelligence Developer 2008 certification (exam 70-452).  Obviously I’m not allowed to talk specifically about the exam, but I did have a few thoughts on it after taking it.

For prep I used the Transcender practice exam pretty much exclusively.  I’ve used them in the past, and you really can’t go wrong with their practice tests.  The flashcards are great!  Also the detailed explanations given for each question in the practice exam(s) are really handy.  That said, the Transcender definitely is not exactly like the test, and there were a few questions I felt unprepared for.  For the most part though it covers the areas you’ll need to know.

The biggest thing I noticed about the exam is that it just felt really dated (probably because it is, I shouldn’t have waited so long to take it).  Since SharePoint 2010 came out, SharePoint is such a huge part of what a BI developer does these days.  PerformancePoint, PowerPivot, Excel Services,  etc,  and the configuration(s) that go along with those services just weren’t covered.  All mentions of SharePoint and PerformancePoint were still geared to SharePoint 2007 and WSS 3.0. 

Another thing I noticed about the exam is that some of the questions were really geared more towards the DBA track in my opinion, particularly around the detailed coverage of db backups.  I also would have expected to see a few more questions surrounding PerformancePoint/SSAS security, as that a topic that comes up almost every time I meet with a client…particularly around dynamic security.

Overall though, I’m really glad to have finally knocked out my first MCITP cert, and will proudly add it to my resume.  In retrospect, I should have taken this test right after I took the MCTS BI test a little over a year ago, but hindsight is 20/20.

I’ve been looking for the solution to this problem on and off for months, and finally stumbled upon the answer. 

The scenario was this:

I placed an SSRS report into a PPS dashboard. The SSRS report has nothing but a gauge, and a table with some related data. For some reason the gauge image only shows up when I have the “show toolbar” option checked, otherwise the gauge shows up as a broken image or red x. In either case, the data for the table always shows up. If I navigate to the report server directly and view the report I have no problems, it only seems to happen from within PPS (both in designer and once deployed).

Apparently when Reporting services is configured to authenticate via “Trusted Account” mode, you need to specify the URL of the Report Server proxy rather than just the URL to the Report Server.

So for example, when configuring the “Report Server URL” in your Performance Point SSRS report you would typically enter it as

http://server/reportserver

however if you’re experiencing the problem described above, try referencing it including _vti_bin in the URL like so

http://server/_vti_bin/reportserver

That seemed to do the trick for me.   Hope this saves someone some frustration.

Also titled – “how can you make it so users only see data that belongs to them…without configuring Kerberos”

By the end of this walkthrough you’ll hopefully have a pretty good understanding of how to implement dynamic security in a Performance Point 2010 dashboard connected to analysis services.  Security in general can be a pretty tricky subject, and if you don’t understand the basics it’s easy to get lost/confused.  I’ll try my best to make this as simple as possible with lots of screen shots.

If you’re not familiar with terms like “Double Hop”, and why you might need Kerberos configured, you should go watch this video http://go.microsoft.com/fwlink/?LinkId=218209 it’s pretty easy to follow, and will probably make the rest of the walkthrough easier to understand. (although, you do not need Kerberos configured to implement this)

Let’s start off by taking a look at our data source options in PerformancePoint.  There are 3 different options when you create a new data source in PerformancePoint as shown below in the highlighted yellow area

 

  Let’s take a minute to talk about each one and how it works

1.        “Unattended Service Account” – This is by far the easiest to implement.  In this scenario you configure a service account in the “Secure Store Service Application” in SharePoint and all PerformancePoint users use those credentials when connecting to your data source.  

2.       “Unattended Service Account and add authenticated user name in connection string” – This method is the same as the first, with one exception.  In addition to using the service account, it also passes the current users id, which can then be retrieved later using the CUSTOMDATA() function in MDX. 

3.       “Per-user Identity” – This option always connects to your SSAS data source as the current logged in user.  HOWEVER, you’ll almost certainly need to have Kerberos configured on your domain in order to make this work….and Kerberos configuration is indeed no small task…..even getting the security needed to configure Kerberos in the first place can be a nightmare.  The only way you won’t need Kerberos, is if your SharePoint installation resides completely on 1 server, AND your SSAS instance and cube also happen to be on that server…….and that shouldn’t be the case.  It should be noted though, that implementing Kerberos is a best practice, and if this option is available to you, it is more secure and recomended.

In order to implement dynamic security we’ll be using the 2^nd option “Unattended Service Account and add authenticated user name in connection string”.  Once you’ve got your data source configured as such, go ahead and create an analytics grid report and place this mdx in it (This is assuming you’re using the [Adventure Works DW 2008R2] SSAS project.  If you’re not, then I’ve highlighted the important parts below, just replace non highlighted parts with something that is relevant to your cube)

WITH MEMBER [Measures].[MyUsername] as CUSTOMDATA()

SELECT

{ [Measures].[MyUsername] } ON 0,

NON EMPTY { [Employee].[Title]} ON 1 

FROM [Adventure Works]

If all has gone well thus far, the results of this should show “MyUsername” with a value of the account you’re currently logged in as….something like “domain\username”.  Now in order to implement dynamic security, this user name will need to physically exist in our cube data somewhere, so that we can compare/match to it.  There are a few different ways you could go about it, but for the sake of simplicity in this example, we’re going to say that there’s a dimension called DimOpportunity that has an attribute called “Account Owner” that contains our user id’s.  Our goal is: 

User should only see opportunities that belong to them based on who they’re logged in as.

In order to make that happen we’ll need to create a new role in our cube, and apply some “allowed sets” to the dimension that passes in our user id and makes the comparison.  To create a new role in your cube you’ll want to

1.       Connect to Analysis Services with SQL Server Management Studio (SSMS).

2.       Select the database your cube is in, expand it, and then right click on rolesànew role

On the general tab of your new role, you can assign the role a name, I’m just going to leave the name as “Role” since this is just an example, but you’ll obviously want to give it a more meaningful name.

After you’ve named your role, click on the “Cubes” page as shown below, and change your Access, and Local Cube/Drillthough values to match what’s shown below (Cube name has been blacked out to protect the innocent).  You'll get some additional prompts asking if you want to enable dimensions for read...yes you do.

 

Next you’ll want to select the “Dimension Data” page on the left hand side, then select the dimension you want to apply the security to.  (remember, this dimension will need to have the actual user names in one of its attributes, so we can compare to it) In my case I’m comparing to Dim Opportunity.

After we’ve selected our dimension, you’ll need to click on the “Advanced” tab and then select the attribute that contains our user name from the Attribute drop down box.  In my case that attribute is Account Owner.  We then need to create an allowed member set.  Allowed member sets grant access to only the attributes that have been specifically assigned…all other attributes not specified in the set are denied.  Since we want the user to see only rows that they are assigned to, we can use the STRTOMEMBER() and CUSTOMDATA() function to define the named set like so:

{strtomember("[Dim Opportunity].[Account Owner].&[" + CUSTOMDATA() + "]")} 

If we want this limitation to apply to other dimensions hanging off our fact, we’ll also need to make sure to check the “Enable Visual Totals” checkbox, as shown above.

We’re almost done!  Next we’ll need to tell our PerformancePoint data source to use our new security enabled role we’ve created when it retrieves data.  To do this we simply open the data source in PerformancePoint Dashboard Designer, and type our role name in the box titled “Role:”.  Since our Role was simply named “Role”, that’s what you see in the screenshot below.

If all has gone well, you should now be able to log into your dashboard and view the data filtered based on who you are.  Hopefully you’ve found this walkthrough helpful, if so gimme some stars.  You can find some additional reading on the subject at the below link.

http://www.sqlmag.com/article/sql-server-analysis-services/protect-udm-with-dimension-data-security-part-2

In the Microsoft BI space, there are several words that seem to get reused quite a bit for product/technology names.  SharePoint, Power Point, Performance Point, Power Pivot, Pivot Viewer.  I've heard multiple people (myself included) use the wrong name when talking about a particular technology, due in part to the names being so similar.  I decided to make a venn diagram to demonstrate the reuse of words, as well as shed a little light on what each technology does...mainly because I thought it would be funny....and also because I am a nerd.  Enjoy.

 

 

Get your link on:

Pivot Viewer - Silverlight BI control

Power Pivot - Excel/SharePoint add in for data analysis

Power Point - Presentation/Slide software

SharePoint - Collaboration

Performance Point - BI Dashboarding