I’m presenting at the Cincinnati SharePoint User Group

I'm presenting at the Cincinnati SharePoint Users Group on Jan 6th 2011. If you're in the area, you should come to the meeting. Link and summary below:

https://cincyspug.securespsites.com/default.aspx

SharePoint 2010 and Business Intelligence

You’ve got data, probably lots and lots of data across multiple systems. Your end users however want timely access to information to make effective decisions and to monitor and analyze performance. Join us for a high level overview and demonstration of what SharePoint 2010 has to offer end users who have Business Intelligence needs. Topics covered will be:

• Excel Services

• PowerPivot

• Visio Services

• Reporting Services (SharePoint Integrated Mode)

• Performance Point Dashboards

Presenter: Tavis Lovell
Tavis Lovell is a Practice consultant with Ascendum Solutions, specializing in SQL Server (Integration Services, Reporting Services, Analysis Services) and database design related to transactional or BI implementations on the Microsoft stack. Tavis is also involved with using SharePoint 2010 for content delivery as it relates to Business Intelligence. When he’s not trying to help the world make better decisions, he spends his time either pretending he’s Jimi Hendrix, or falling down the sides of mountains on a snowboard.

Configuring Visio Service in Sharepoint 2010 to use Secure Store Service

I recently started playing around with Visio Services in Sharepoint 2010. I set up data connections to SQL Server so I could display data for a few of the items in my drawing and everything was working fine in Visio, however when I viewed it in the SharePoint Web Part I started getting the following error:

“Visio Services was unable to refresh external data connections in this diagram because an underlying data source is unreachable. To resolve this issue, try again or ask your system administrator to make sure that all underlying data sources are available.”

One of the ways to get around this error and to get your data flowing freely again is to configure the Secure Store Service in Sharepoint 2010. You’ll need admin rights and access to SharePoint Central Administration to do so. Start off by navigating to the following page:

Central Administration—>Application Management—>Manage Service Applications—> Secure Store Service.

Select “New” from the edit ribbon and fill out the requested info, taking special note of what you put in “Target Application ID” as you’ll need that later. You’ll also need to select “Group” for the “Target Application Type”. Once complete you should have something similar to the screen below. Once finished click next.

clip_image001

You should now be prompted to enter what fields you want to provide values for. I used Windows Auth for my data connections, so I didn’t need to change anything on this screen (note this screen is not prompting you to type in the actual user name and password…only what info you’ll be providing later on) Once finished click next.

The third screen is pretty self-explanatory. I was setting this up in a demo virtual machine so I configured it as shown below. Once you’re done click OK.

clip_image002

Once you click ok you’ll be taken back to the screen we started on. You should now see a new item with the “Target Application ID” you entered in step one. Select the item by checking the select box on the left hand side, and then click “Set Credentials” up at the top (highlighted yellow in the picture below)

clip_image003

Now just enter in the credentials you want to use to access the data in your Visio drawing and then click OK.

Only one last thing to configure before we wrap this up. Navigate to Central Administration --> Application Management—> Manage Service Applications—> Visio Graphics Service.

Click on the “Global Settings” link and in the “Application ID” textbox type in the name you gave your “Target Application ID” in our first step and then click OK.

clip_image004

That should do it, no more nasty error messages, and your Visio drawing should be showing your data. As usual, feel free to send your questions or comments to the email on my contact page.

Using T-SQL UNION and UNION ALL

The UNION statement does pretty much exactly what it says, It takes two (or more) result sets and combines (or UNIONs) them together into one result set. In order to UNION the result sets together there are a few things you'll want to check first.

· Each result set must return the same amount of columns. - So if the first query returns 5 columns....all other UNIONed queries must also return exactly 5 columns, otherwise you'll end up with an error.

· Columns names for the UNIONed result set are defined in the first SELECT statement of the UNION query. - So if you want your first column to be called "Col1" in the result set, make sure you have it named as such in the very first select statement.

· You can have one and only one ORDER BY statement in a UNION. The ORDER BY statement must appear in the final select statement of the UNION query, and will order all data across the entire set.

In this example I'm going to use 2 tables named "TableA" and "TableB". Both of the tables have one column in them named "ID" that contains integer values. "TableA" will contain all even number from 1-10, and "TableB" will contain all odd numbers 1-10. In addition to that, each table will contain all numbers from 1-5. To recap:

TableA contains the following values (1, 2, 3, 4, 5, 6, 8, 10)

TableB contains the following values (1, 2, 3, 4, 5, 7, 9)

If we wanted to write a query that would return all DISTINCT values from each table in one result set, we could do so using the UNION operator like so:

SELECT
      a.ID
FROM
      TableA a
UNION
SELECT
      b.ID
FROM
      TableB b
ORDER BY
      ID

The results of this query would look like this:

ID

-----------

1

2

3

4

5

6

7

8

9

10

Which is exactly what we expect. An important note here is that the ORDER BY statement only appears once at the very end of our query (as mentioned before). Without the ORDER BY statement, we couldn't guarantee the order of our results.

While the UNION statement returned all the DISTINCT values from each of our tables, let's suppose we had a need to see every value all in one set, even the values that were repeating. We can accommodate such a need by simply using "UNION ALL" instead of just UNION, as seen below

SELECT
      a.ID
FROM
      TableA a
UNION ALL
SELECT
      b.ID
FROM
      TableB b
ORDER BY
      ID

The results of this query would be as follows:

-----------

1

1

2

2

3

3

4

4

5

5

6

7

8

9

10

We now see every single row in each of the SELECT statements, even if there are duplicates.

While we've only been using two SELECT statements in our examples, you can UNION just about as many SELECT statements together as you want (or at least as many as would be practical) as long as they meet the criteria we discussed at the beginning of this article. So for instance if we wanted to add a third table our query might look something like this:

SELECT
      a.ID
FROM
      TableA a
UNION
SELECT
      b.ID
FROM
      TableB b
UNION
SELECT
      c.ID
FROM
      TableC c
ORDER BY
      ID

I'd say that probably wraps up our use of UNION. Hopefully someone finds this helpful, and as always if you've got any questions feel free to shoot me an email at the address on my contact page.

 

Finding Duplicate Values In A Database Table

Duplicates values are a pretty common problem I run across in databases. There are several precautions you can take to prevent dups in your DB, like using constraints, primary keys, etc. Once duplicates make their way into a table, they can cause all kinds of unexpected/undesirable results. This post is less about prevention though, and more about finding duplicates once they've made their way in. For example purposes I've built a table called "DupExample" that has two columns "Col1" and "Col2" with the following data in it:

Col1 Col2

----------- ------------

1 a

2 b

3 c

4 d

5 e

2 f

4 d

4 d

If we were wanting to find duplicate values in "Col1" we could do so with the following query.

SELECT
      d.Col1,
      COUNT(*) 'NumOfDups'
FROM
      DupExample d
GROUP BY
      d.Col1
HAVING 
      COUNT(d.Col1) > 1 

The results of this query will show you the values that are duplicated in "Col1", and how many times they're duplicated, as seen below:

Col1 NumOfDups

----------- -----------

2 2

4 3

Sometimes the uniqueness of a row in a table is defined by more than one column. In this circumstance we'll need to include the other columns that make up the unique key in our GROUP BY and HAVING clause. If we wanted to find rows that have duplicate values across both "Col1" and "Col2" we could modify our query to the following:

SELECT
      d.Col1,
      d.Col2,
      COUNT(*) 'NumOfDups'
FROM
      DupExample d
GROUP BY
      d.Col1,
      d.Col2
HAVING 
      COUNT(d.Col1) > 1
      AND
      COUNT(d.Col2) > 1

The results of this query would show us that there is actually a triplicate value in our test data, as seen below.

Col1 Col2 NumOfDups

----------- ------------- -----------

4 d 3

Now that you've figured out which values have been duplicated, your next step should be trying to figure out how the dups are being created, and how you can prevent them in the future (aka fix the problem, not the symptom). Also, as a final word of caution, research your duplicates before you start deleting rows. Do other tables hold a foreign key that points to the duplicate row you're about to delete? Are there constraints in place that will prevent you from deleting a row if it is a FK in another table? (I hope so)

Well, that's a start on finding duplicates within a single table. Hope you found it useful.