Archive

Posts Tagged ‘Document Stuff’

SharePoint 2010, User Profile Synchronization and Forefront Services… Chapter 2

January 9, 2012 Leave a comment

In the aftermath:

To continue the story, see Chapter 1 for the lead in, last night Windows updates were applied and the whole server environment restarted.  As a result all the errors except the User Profile Synchronization/Service has been resolved.  An interesting note is the ASP.NET Expired Sessions error is still showing but on the data server, the job exists, is named correctly and is running without issue.  It may have something to do with the change of networks, etc.  and may require adding a static path to the box because it may be able to find the server.

So, truly this blog is going to focus on UPS.  Yesterday, I wasn’t so sure…

The Current Errors:

  • Verify that the critical User Profile Application and User Profile Proxy Application timer jobs are available and have not been mistakenly deleted.
  1. A required timer job for a User Profile Application or User Profile Application Proxy is missing.
  2. The repair action will recreate missing timer jobs required for the User Profile Application or User Profile Application Proxy.

Microsoft send you to the following link.

Did those steps and nothing was resolved.   This is because a non-functioning UPS service never completely deleted.  It took restarting the server to resolve.  Now when I return back to Central Admin, the error above is gone.

image

Skipping Expired Sessions for now, addressing User Profile Synchronization Issues

  1. Go through and verify that the Farm Account is set up correctly
  2. Make sure that it has local permissions on the server so it can start and run the forefront serfvices
  3. Stop and Restart the Time Service. 

Since that didn’t effect all the changes, went into the error log, found that there were a lot of DCOM errors…

My First Clue?

image

Digging a little deeper?

image

Hundreds of these errors…

image

Note the CLSID and the user account…

Regedit search returns…

image

A quick trip into Component Services… tracing down the DCOM list to WAMREG… my farm account did not exist.  I added it and gave it full rights. 

image

The Remote Launch and Activation are probably overkill but at this point…I can always scale it back.  Back to powershell…

image

Ok, still no love from the server.  But with luck there should be a new error…nope but still more stuff to check.

 

You will probably get sick of updating the properties of the Forefront identity services but until this is resolved (permissions) they will keep switching to disabled.

  • Expired sessions are not being deleted from the ASP.NET Session State database.
  1. If expired sessions are not deleted, the server that hosts the ASP.NET Session State database may run out of disk space and the SharePoint farm may cease to function.
  2. Connect to the server that hosts the ASP.NET Session State database, start the SQL Server Agent service, and verify that a DeleteExpiredSessions job is registered for the database. If you are using SQL Server Express, the SQL Server Agent service is not available.

I set up the job On SQL and verified that runs on the server as an unattended job.  My suspicion is that there may be a permission issue between SharePoint and SQL.   So first it’s off to SQL to check logs…

Nothing.  The SQL Server is happy and running just fine.

Time to stop here and wait for another reboot.  Hopefully the additional changes will fix the outstanding issues.

Advertisements
Categories: Microsoft, SharePoint Tags:

SharePoint 2010, User Profile Synchronization and Forefront Services… Chapter 1

January 5, 2012 1 comment

Overview…

This will be a series unless the powers that be either infuse detailed SharePoint knowledge into my freaking skull or it was just a missed step along the way to the yellow brick road.  Either way, here’s the scoop…

SharePoint Profile Synchronization Service no workee…

Yup, that pretty much sums it up.  But from the user side, I get to hear…

“Hey!” <—yes, they actually say this

“Why doesn’t _____ “ fill in the blank “show up in my organizational browser?”

Hmmmm….

Well, there have been several challenges to this and I’m putting this up on my blog in hopes that if you run into this, my exploration will help.

My Background

Over the past three years I have devoted a lot of time to SharePoint.  In the past, I’ve worked with 2003 and the place I’m currently at was running this as a proof of concept turned live.  Hmmmm… bet you’ve never run into that before?  Well, I’ve got a lot a skills with SQL and Development stuff so I just dove right in. 

Prepare Yourself

Ok, if you are going to deal with SharePoint, it’s best to partner up with your opposite in the IT world.  If you are a developer, work with a Systems Admin.  If you’re an SA?  You better partner up with a Dev.  Bottom line, if you are going solo on this, make dang sure you’ve got a reading budget.  You’re going to learn a lot about the other side of the shop.  This is a good thing.  Security, and all, makes this stuff tick.  PowerShell is an object oriented tool.   In other words, it’s not Grand Pappy’s command line script tool.  So plan some time to learn.

The setting

SharePoint 2010 running in a simple farm schema (single web server and separated db server).  Since the service is serving a group WAY UNDER the 10k user boundary, this should be more than sufficient.  Granted, the preferred configuration is a couple of web servers, a search server and a db server but I’m lucky to have the two machines.

Suspect Number 1

Forefront..

image

These two little services are critical to user profile synchronization.

image

Shows up ever so nicely on the admin server.

The set of errors I’m going to delve into…

  • Databases require upgrade or not supported.
    • The following databases require upgrade because their versions are older than the backwards compatible range:
      WSS_Search_SHAREPOINT2010.
    • Use Upgrade-SPContentDatabase to upgrade Content databases, or psconfig.exe to upgrade other databases. If some databases are too new, check the Patch Status page to find out if the current server are missing SharePoint software patches.
  • Verify that the critical User Profile Application and User Profile Proxy Application timer jobs are available and have not been mistakenly deleted.
    • A required timer job for a User Profile Application or User Profile Application Proxy is missing.
    • The repair action will recreate missing timer jobs required for the User Profile Application or User Profile Application Proxy.
  • Databases running in compatibility range, upgrade recommended.
    • The following databases have versions that are older than the current SharePoint software, but are within the backwards compatible range:
      Social DB,
      User Profile Service Application_SocialDB_0e088f2b178047cb9167c6878761582c,
      WebAnalyticsServiceApplication_StagingDB_f0a8f080-7bab-42a6-903d-7621f622d448,
      SharePoint_Config,
      Search_Service_Application_CrawlStoreDB_0986cb714f9d486cbc8af012a3beb1be,
      Search_Service_Application_PropertyStoreDB_e2ecc325f9994d50a7c354eb64d4dcee,
      SharePoint_AdminContent_c13f8581-4ed7-48f4-bab9-b30fb200be3a,
      WSS_Content,
      WSS_Content_MySitesHostDB,
      Search_Service_Application_DB_f25bbe2e140346c6aa3da75c17b292c8,
      Managed Metadata DB,
      Profile DB,
      User Profile Service Application_ProfileDB_06c87034ec9e4bf68a557185194b4a92,
      WSS_Logging,
      WebAnalyticsServiceApplication_ReportingDB_ae0ab5c7-286b-400d-b07c-ecd778e081e3.

    • To achieve optimal results from these databases, use Upgrade-SPContentDatabase to upgrade Content databases, or psconfig.exe to upgrade other databases.
  • Expired sessions are not being deleted from the ASP.NET Session State database.
    • If expired sessions are not deleted, the server that hosts the ASP.NET Session State database may run out of disk space and the SharePoint farm may cease to function.
    • Connect to the server that hosts the ASP.NET Session State database, start the SQL Server Agent service, and verify that a DeleteExpiredSessions job is registered for the database. If you are using SQL Server Express, the SQL Server Agent service is not available.
  • Verify each User Profile Service Application has a My Site Host configured
    • Without a My Site Host, end users will not be able to use personal sites or people profiles. There will be no UI for consuming features offered by the User Profile Service.
    • To set up a My Site Host for an existing User Profile Service, you can use the Set-SPProfileServiceApplication commandlet. This commandlet takes in a parameter for MySiteHost.
  • People search relevance is not optimized when the Active Directory has errors in the manager reporting structure.
    • In Active Directory, only company leaders should have the ‘manager’ property set to NULL. As a result of errors, the Active Directory can incorrectly have the ‘manager’ property set to NULL for other users that can cause a decrease in people search relevance. By specifying the actual leaders of the company, these inconsistencies are not taken into account and the relevance problem is corrected
    • Specify the company leaders explicitly. Use the following PowerShell commands: $upap = Get-SPServiceApplicationProxy [appid]; Add-SPProfileLeader $upap [Domain]\[UserName]. Run ‘Get-SPProfileLeader $upap’ to check whether the leader was successfully added. As a last step, run a full crawl on the content source containing the start address (URL) of the user profile application

Upgrade the Content Databases…

NAME
    Get-SPServiceApplication

SYNOPSIS
    Returns the specified service application.

    ——————EXAMPLE———————-

    C:\PS>Get-SPServiceApplication

    This example returns all service applications in the farm.

 

PS C:\Users\spadmin> Upgrade-SPContentDatabase -?

NAME
    Upgrade-SPContentDatabase

SYNOPSIS
    Upgrades a content database.

 

SYNTAX
    Upgrade-SPContentDatabase [-Identity] <SPContentDatabasePipeBind> [[-ForceD
    eleteLock] <SwitchParameter>] [-AssignmentCollection <SPAssignmentCollectio
    n>] [-Confirm [<SwitchParameter>]] [-ServerInstance <SPDatabaseServiceInsta
    ncePipeBind>] [-UpdateUserExperience <SwitchParameter>] [-WhatIf [<SwitchPa
    rameter>]] [<CommonParameters>]

    Upgrade-SPContentDatabase [[-ForceDeleteLock] <SwitchParameter>] -Name <Str
    ing> -WebApplication <SPWebApplicationPipeBind> [-AssignmentCollection <SPA
    ssignmentCollection>] [-Confirm [<SwitchParameter>]] [-ServerInstance <SPDa
    tabaseServiceInstancePipeBind>] [-UpdateUserExperience <SwitchParameter>] [
    -WhatIf [<SwitchParameter>]] [<CommonParameters>]

DESCRIPTION
    The Upgrade-SPContentDatabase cmdlet upgrades a SharePoint content database
    . When the Upgrade-SPContentDatabase cmdlet is run, an upgrade of an existi
    ng content database attached to the current farm is intiated. This cmdlet b
    egins a new upgrade session, which can be used either to resume a failed ve
    rsion-to-version or build-to-build upgrade of a content database or to begi
    n a build-to-build upgrade of a content database.

    For permissions and the most current information about Windows PowerShell f
    or SharePoint Products, see the online documentation (http://go.microsoft.c
    om/fwlink/?LinkId=163185).

 

RELATED LINKS
    Dismount-SPContentDatabase
    Get-SPContentDatabase
    Mount-SPContentDatabase
    New-SPContentDatabase
    Remove-SPContentDatabase
    Set-SPContentDatabase
    Test-SPContentDatabase

REMARKS
    To see the examples, type: "get-help Upgrade-SPContentDatabase -examples".
    For more information, type: "get-help Upgrade-SPContentDatabase -detailed".
    For technical information, type: "get-help Upgrade-SPContentDatabase -full"
    .

So, Running Get-SPServiceApplication returns…

PS C:\Users\spadmin> Get-SPServiceApplication

DisplayName          TypeName             Id
———–          ——–             —
Access Services      Access Services W… ed255b7b-1ff8-4cb7-ab02-76a8129b9dbe
Secure Store Service Secure Store Serv… 31663d9f-1df0-49f3-9e4c-829ff9b41c0f
State Service        State Service        ba90ad16-f558-452c-94bf-5a2fa89bb73a
PerformancePoint … PerformancePoint … 95727ef9-df54-4dbf-b77b-2fda042ac240
Visio Graphics Se… Visio Graphics Se… d38f440f-5ae2-46a6-ad45-d80972615b48
Managed Metadata … Managed Metadata … ba182a51-8ba1-4754-82b4-0b667fd04136
Web Analytics Ser… Web Analytics Ser… ebf0155e-a788-44aa-819a-008a63234ccb
Excel Services Ap… Excel Services Ap… 833183c7-74a7-4e04-8b47-d2ad90235a4e
Security Token Se… Security Token Se… 330b2900-220a-499d-9aa8-05f2533b0204
Application Disco… Application Disco… 964478c1-12e1-44a0-8096-41b6523685d1
WSS_UsageApplication Usage and Health … 6c6a2cea-972a-46e5-a6d9-667df188a85e
Search Administra… Search Administra… a8ce1963-ac57-4c80-9776-cf392a0a3817
Search Administra… Search Administra… 3b01c8af-db8a-4a1a-9a58-ed45b457dcf0
Word Automation S… Word Automation S… fc27dff0-ab89-4fa8-a7d7-23403ffd36b8
Application Regis… Application Regis… ee04f502-6fcc-439b-8c94-8883328098a2
User Profile Serv… User Profile Serv… 0ffcc1b2-0775-41f8-8329-aef0a8b96b29
User Profile Serv… User Profile Serv… 79bfd459-4006-473d-9032-1d4ccaa5d5a2
Business Data Con… Business Data Con… 8f0c2923-ff36-4ebb-a968-bd8ffbed9e67
SharePoint Server… SharePoint Server… 2092cb84-096c-4c24-93eb-86cbf1a56db5
Search Service Ap… Search Service Ap… fda2a209-7acc-4dd8-a211-d9601b179646
Search Service Ap… Search Service Ap… 682b9774-3569-47e2-b669-58560b76a65d

There’s one or two to maintenance…

Running get-help Upgrade-SPContentDatabase –examples returns:

PS C:\Users\spadmin> get-help Upgrade-SPContentDatabase -examples

NAME
    Upgrade-SPContentDatabase

SYNOPSIS
    Upgrades a content database.

    ————————–EXAMPLE——————————

    C:\PS>$contentdb = Get-SPContentDatabase | Where-Object {$_.Name -match "SQ
    LSERVER1\WSS_Content"}

    C:\PS>Upgrade-SPContentDatabase -Identity $contentdb

    This example selects a specific content database that is currently attached
     to the farm, and then initiates an upgrade session. This process continues
     until the upgrade is complete and might take some time, depending upon the
     amount of changes that have to be made during the upgrade.

Ok, So I tried and the result was…

Confirm
Are you sure you want to perform this action?
Performing operation "Upgrade-SPContentDatabase" on Target
"WSS_Content_MySitesHostDB".
[Y] Yes  [A] Yes to All  [N] No  [L] No to All  [S] Suspend  [?] Help
(default is "Y"):y
WARNING: Database [SPContentDatabase Name=WSS_Content_MySitesHostDB] cannot be
upgraded.

Yes, that helped a lot didn’t it?

Checked the server log:

SQL Database ‘WSS_Content_MySitesHostDB’ on SQL Server instance ‘Data’ not found. Additional error information from SQL Server is included below.

Cannot open database "WSS_Content_MySitesHostDB" requested by the login. The login failed.
Login failed for user ‘<domain>\spadmin’.

Ah…  this I can fix…

After fixing the permissions on the database,   I got the upgrade to run.  More research will be needed here to see why the permissions got sideways…

So, both upgraded, now it’s back to the health analyzer to see what’s what…  While it was running, I went back to check on FOREFRONT…

image

A service attempted to access and now the IMSS is set to Disabled… hmmmm… “Are We There Yet?”  NO!!!!

Results from PowerShell

PS C:\Users\spadmin> Upgrade-SPContentDatabase -Identity 1de8dd7f-ff58-4f7f-b1f3
-97509b830885

Confirm
Are you sure you want to perform this action?
Performing operation "Upgrade-SPContentDatabase" on Target
"WSS_Content_MySitesHostDB".
[Y] Yes  [A] Yes to All  [N] No  [L] No to All  [S] Suspend  [?] Help
(default is "Y"):y
100.00%
PS C:\Users\spadmin> Upgrade-SPContentDatabase -Identity e2f2b0e2-452e-4e3f-b695
-1e85f38d90d9

Confirm
Are you sure you want to perform this action?
Performing operation "Upgrade-SPContentDatabase" on Target "WSS_Content".
[Y] Yes  [A] Yes to All  [N] No  [L] No to All  [S] Suspend  [?] Help
(default is "Y"):y
100.00%
PS C:\Users\spadmin>

So after that, still having issues.  Some research said to re-run the configuration wizard.  I figure that since change permissions, this might have more success (already tried don’t ya know).  Now this is not the FARM configuration wizard.  The products configuration wizard is located under installed products in programs…

<waiting while the wizard runs – Finished with success>

Ok, so that did nothing.

Hmmm… try a different issue then come back.

Asp.Net issue looks interesting…

So I followed the steps on Dave Pileggi’s blog

I’ll stop here for now.  More later…

Categories: Microsoft, SharePoint Tags:

Facebook subscriptions… I don’t want ‘em

December 16, 2011 Leave a comment

Facebook, in their ever increasing attempt to make everyone’s information right in your face, enter the “Subscription”. 

Drum roll please.

Is this a good thing?

If I were a product or a witty on air personality? Sure

Perhaps even if I were not so witty or even a personality… maybe… if I wanted my words to be broadcast in the new transmission medium called the web…

Ok, it isn’t so new… the web that is…

<queue “The Dark Side of the Moon”>  and the lunatics are on the grass…

So get rid of ‘em.

Turn ‘em off…

Step 1: Click on the little down triangle by HOME

Step 2: Click on Privacy Settings

Step  3: Click on Edit Settings in the How You Connect Section image

Step 4:  Click on the Subscribers Tab link in the How You Connect Dialog Box – by the way, the text here may be different from what you see.  First time through it was something like “to change settings in your subscriptions…” 

image

Step 5: Turn off the Subscribers

image

Happy turn off Facebook socialization day…

Some Links to check…
Facebook Subscriptions: 5 Warnings

Report: Facebook Changes Could Mean More Security Risks

Facebook Privacy: Uncovering 5 Important Settings

Categories: Article Tags:

Oh Hail the Mighty Customer… or is it Oh Hell!?

December 7, 2011 Leave a comment

Openers

As I started down this train of thought…

Visions of the future crowded in…

Then a ditty popped into my head and it’s ok now.  I mean, what do you do with a drunken sailor? Especially early (err..lie) in the morning. 

This is about Customers and their ways of expressing what they want in contrast to how a lot of technical experts (Consultants, programmers, architects…you name it) interpret those requirements.  I mean, it’s all covered with SDLC or Agile right?  Personally, Agile seems to me a better approach.  As long as the project remains a manageable size.  What do I mean by this?

The Grand Example

Perhaps it’s the Grand Illusion?  Yes? No, STYX!… To keep this short, I’ll use a very basic example where a customer provided me with an input form and a report/input form.  Not really grand…but it will work…

To break it down:

The system definition:  Provide a means by which exceptions generated by customers could be recorded and reported on.  The reported information needs to be generally available to specific groups and easily maintained.  There are two processes by which this information is obtained.  A daily review and an on demand special review.  Kind of looks like:

image

The normal path on the left, occurs daily.  Exceptions are reviewed, reported and resolved.  The “Special Case” on the right only happens on demand and all records reviewed are accounted for.  Those with exceptions follow the same basic path as the daily review of exceptions.

The first pass of the forms received looked something like…

An Input Form;

image

and the combo form:

image

The Input Form was pretty easy to start with to begin the database modeling portion.  It had some selection entries, basic business processes etc.  So here’s how that part went down…

You’re right but I’m right… conveying why the physical model is different

The customer in this case was pretty user savvy with databases.  Running queries were second nature.  ‘splaining how the tables were derived was initially a challenge.  Now, the easy approach would be to take the basic assumptions from the initial design discussion, run back to the lab and crank out a prototype.  Then negotiate change requests…  I didn’t want to do that this time and it was important that the customer fully understood the data design for future support issues.

When is a Model a Model and how do you get there?

One of the bigger challenges I run into is turning the concept from idea to model.  The customer always has a vision.  The vision is always the right one. It’s just up to you to build it, right?  You get there but it’s after cost overruns, delays, and other unsightly events. 

Or worse yet, the customer has a vision but so does the architect.  Then the customer’s vision is taken as just so much advice and adhered to where convenient.  Under that scenario, nobody is happy with the outcome.  The customer isn’t happy because the final product is not what they requested and the architect is unhappy because everybody thinks the baby is ugly.  Back to cost overruns and hurt feelings.

So, back to the scenario at hand.  So far, all that exists are some drawings of a potential system and it’s time for a little show and tell.  Now, taking time out to go and create a database ruins the moment and delays the process so here’ s where a prototyping tool like Microsoft Access comes in real handy.  Even better if the the customer has a smattering of experience and you can drive them to drink …no… create the structure.

Creating the Model as a basis of discussion

Let’s face it, the best system is define/designed when all parties have a clear understanding of what the point is.  Too often have I seen a construction project go awry because the end goal was not understood.  Going back to the original request.  Had I just ran off with the drawing and a base understanding, the end product would have been something other than requested.  Never happens right?  Always a clear understanding, yes?

This is where build and discuss is such a great thing and really stresses RAD (rapid application development).  I’ve worked with some interesting tools for model driven architecture and they all have their strong and weak parts.  Bottom line, use the tool that you are comfortable with and can show results quickly.  I like Access 2010 because you have all the parts there and upsizing to SQL is a snap.

Back to the process and the application under discussion.  What appeared to me as a report, actually turned out to be a hybrid between data gleaned from the system plus some additional manually input data stored as it’s own unique set.  Not only that but a whole other set of requirements came out of the modeling process.  It took quite some time but even at it’s rough stage, the final product is already shaping up into something the customer is happy with and can see additional offshoots for future enhancements.  This is a much better approach than building some ornately complex system that only you can support.  Scary thing is that some folks do this unintentionally.

Structure out of chaos

Software development requires a structure mind, a structured process and a structured result.  That works very well when you are creating a finite system of inputs and outputs.  For example, a real estate listing system is concerned about the content which is composed of properties.  There’s a pretty finite definition of a property and it’s uses. 

Where chaos comes in to play has to do with systems that track or deal with variable content.  While there is only one way to accurately describe a house in a listing, there are a lot of ways to creatively change or embellish the basic data such that the house stands out or appeals to a specific group.  Which is why there list such things as Equal Housing Laws.  Some Real Estate professionals were a bit more creative than others…

So how does this come around to Modeling?

When you build the app with the customer in small bits with lots of discussion, the application starts to take shape from some basic building blocks.  Asking key questions about values and if those values should be limited to a specific set, yield sub tables with relationships where initially there were none.  When you start out, you basically are working from the logical model of the data structure.  As the discussion progresses, you can start to build out the physical model for the application.   With a tool like Access, you can quickly throw up some basic forms and ask questions like:  is this how you envisioned it?  What happens when “this” event occurs?  How do you see the data changing over time?  Experience helps tune your crystal ball for those futuristic type questions but you get the gist.

T-SQL Pivot and UnPivot… What?

December 5, 2011 Leave a comment

Ok, put away the Excel.  Why am I mentioning Excel?  Hmmmm… maybe you should learn a bit about Pivot tables.  From a user perspective they are kind of important.  Sales reports are often targets for this treatment where you might have a region list that goes down and sales by quarter going across the top.  You all have seen reports like that.  Well, Pivot and Unpivot operators do that right within the sql results.

A note about performance.  Here’s a good blog about CASE versus PIVOT.   While it’s referencing 2005 not 2008 versions, the reasoning stays the same.  But if you are set and determined on using PIVOT with multiple columns, the following will help…

Building the Pivot

First take a simple query using Northwind

SELECT     Orders.OrderID, Orders.OrderDate, [Order Details].UnitPrice, [Order Details].Quantity, Orders.ShipCity
FROM         Orders INNER JOIN
                      [Order Details] ON Orders.OrderID = [Order Details].OrderID

You get:

image

You’ve got the Order ID, OrderDate, Price, Qty and where it went.  The request though is for the total number of orders by city broken out by year with dollar totals…  Sure, you can do that…

Now you’ve changed your query to:

SELECT    
    Count(*) as OrderCount
    , Year(Orders.OrderDate) as OrderYear
    , Sum([Order Details].UnitPrice *[Order Details].Quantity) as OrderTotal
    , Orders.ShipCity
FROM         Orders INNER JOIN
                      [Order Details] ON Orders.OrderID = [Order Details].OrderID
Group By Year(Orders.OrderDate)
    , Orders.ShipCity 

And you get:

image

Hmmmm…  Maybe some additional sorting before you hand off the result.

SELECT    
    Count(*) as OrderCount
    , Year(Orders.OrderDate) as OrderYear
    , Sum([Order Details].UnitPrice *[Order Details].Quantity) as OrderTotal
    , Orders.ShipCity
FROM         Orders INNER JOIN
                      [Order Details] ON Orders.OrderID = [Order Details].OrderID
Group By Year(Orders.OrderDate)
    , Orders.ShipCity      
Order By ShipCity, OrderYear             

Returns

image

Ok, it’s ready now.  Just hand it off to the business analyst who then runs it into Excel as a data sheet and constructs a pivot table/chart off the data.

Depending on the environment, who’s in charge of the “truth” behind the data.  Sure, this could be good enough.  Really.  I mean, “You can’t handle the truth”, right?

For this purpose, we’re going to take the data into a pivot table and deliver it up that way.  Maybe our customers don’ t know about pivot tables.  Maybe they do and don’t have the time to mess around with Excel.  Whatever the reason, you’re the pivot table provider.

Now, getting the two columns in PIVOT seem to be a bit problematical.  So, I worked around it by first creating a sales pivot table:

SELECT
    ShipCity    
    , [1996] as Y1
    , [1997] as Y2
    , [1998] as Y3

    From
    (SELECT    
            Orders.ShipCity
            , YEAR(Orders.OrderDate) AS OrderYear
            , [Order Details].UnitPrice * [Order Details].Quantity AS OrderTotal
         FROM          Orders INNER JOIN
                             [Order Details] ON Orders.OrderID = [Order Details].OrderID
    ) AS PVT
    PIVOT
    (
    Sum(OrderTotal) FOR OrderYear IN([1996],[1997],[1998])
    ) as Child
Order By ShipCity

Which Returned:

image

Wait…

What’s missing?

Order Counts are gone.  Well, try as I might, the support for multi columns doesn’t seem to exist and is a pretty common complaint.

So I took a little trip down MSDN way and looking at the documentation…

image

Yup…  Only one group pivoted.

Ok, so just for fun, combine the above example with CTEs, etc for this:

With pvtConcat
AS
(
    SELECT
        ShipCity    
        , [1996] as C1
        , [1997] as C2
        , [1998] as C3

        From
        (SELECT    
                ShipCity
                , YEAR(OrderDate) AS OrderYear
                –, [Order Details].UnitPrice * [Order Details].Quantity AS OrderTotal
                , Count(OrderID) as OrderCount
             FROM          Orders
             Group By ShipCity, YEAR(OrderDate)
        ) AS PVT
        PIVOT
        (
        Sum(OrderCount) FOR OrderYear IN([1996],[1997],[1998])
        ) as Child
    )

Select ShipCity, C1, C2, C3
into #_concat
from pvtConcat;

With pvtMerge
AS
    (
        SELECT
            ShipCity    
            , [1996] as Y1
            , [1997] as Y2
            , [1998] as Y3

            From
            (SELECT    
                    Orders.ShipCity
                    , YEAR(Orders.OrderDate) AS OrderYear
                    , [Order Details].UnitPrice * [Order Details].Quantity AS OrderTotal
                 FROM          Orders INNER JOIN
                                     [Order Details] ON Orders.OrderID = [Order Details].OrderID
            ) AS PVT
            PIVOT
            (
            Sum(OrderTotal) FOR OrderYear IN([1996],[1997],[1998])
            ) as Child
    )
select * into #_merge from pvtMerge;

Select cc.ShipCity, cc.C1, cc.C2, cc.C3,  pm.Y1, pm.Y2, pm.Y3  from #_concat cc Inner Join #_merge pm on cc.ShipCity = pm.ShipCity

You get:

image

Now wasn’t that the cat’s MEOW…

Mentioned was unpivot.  Works in a similar fashion and the syntax is (from SQL Books Online):

–Unpivot the table.

SELECT VendorID, Employee, Orders

FROM (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5 FROM pvt) p

UNPIVOT (Orders FOR Employee IN (Emp1, Emp2, Emp3, Emp4, Emp5) )AS unpvt;

Categories: Microsoft, SQL Server Tags:

Normalization What?

November 17, 2011 Leave a comment

History

A fellow by the name of Edgar F. Codd came up with the concept of Normalization in the early 70’s.  From there additional contributions were made by Ronald Fagin, Raymond Boyce (with Codd) and C. Zaniolo.  From 1981 to 2002 the Normalization front was pretty quiet until C. J. Date, Hugh Darwen and Nikos Lorentzos added the 6th normal form.  All of this mainly pertains to relational databases the support transactional data.  Why do this?  There are lots of good reasons to apply some depth of normalizations.  One of the primary ones is data integrity.  Normalization through the reduction of duplicate objects within the database enforce data integrity by controlling the objects that can be introduced.  Objects can anything from a single field value to the whole database.  Another reason is for ease of data updates. 

Example

You have a record that describes a house.  Some attributes of the record could include roof type, number of bathrooms, kitchen counter type, room floor type and so on.  Of these attributes roof type, kitchen counter type and room floor type can be problematic for updates and consistency.

image

Why? 

You’ve written the application that only allows certain values.  That will keep it consistent right?  Sure.  That works.  But what happens when you decide that “Shake” is not what the value should be?  You need to update it to “Cedar Shake”.  Ok, that’s simple, just replace all instances of Shake with Cedar Shake.  You’re done now.  No problem right?  Well, what happens if you have a million records and each record has to be inspected for the value of roof type.  Does it contain “Shake”? If yes, replace and if no, skip to the next record.  A bit labor intensive but doable.  I’ll skip the point about going into record and table locks and other nastiness.  If you applied some normalization to the database and decided that something with the word “type” is a natural for separate table you’re well on your way.

A simple answer

With the same example, with a “Roof Type” table, you simply have to update the description in the type table.  How does this work?  With a simple type table you have two items, the Key and Value.  The important thing to remember is that keys are immutable.  Once created they cannot be changed.  The value is updatable and can be modified.  In your main table, the “Roof Type” key is stored in the house record.  When you display the record, you join the main table with the type table and display the “value”.

image

Data Type for the key fields has been changed to int type to match the field type in each of the type tables

image

image

image

The final data model looks like:

image

This way when you update the value in any of the “Type” tables, the change is reflected n the main table without having to update it at all.  This means you only have to process one update to affect every matching record.  So if your table had a million records, the roof type in question existed on a quarter of those records, you updating once instead of 250,000 times.  Make sense to me…

Summary

This is just a quick snack on database design and why normalize.  Hopefully, there’s enough here that if you do not know what normalization is that you’ll be able to read up more on the process.

A good place to start is this overview of normalization.

SharePoint 2010 The Sites

November 17, 2011 Leave a comment

Site Types? Why so many?

This is the first of several blogs going over SharePoint 2010 Sites.  One of the first things I’ve found about SP is that it is huge with many options.  Is that just a SharePoint thing or not?  Well, if you tried to employ any of the framework apps, you’ve found out that SharePoint isn’t alone here.  What is exceptional is its integration with other Microsoft tools and applications.

So, yes the makers at Microsoft are not to be out done and yes, they created a lot of site templates to start with.  Oh, you can also customize and make your own templates.  In fact, that’s probably where you will eventually end up unless you work for Microsoft and are demoing the the stuff.

Ok, so what are they you ask?

Well, first lets look across the top at the categories.

You’ve got your:

  • Blank and Custom Sites
  • Collaboration Sites
  • Content Sites
  • Data Sites
  • Meeting Sites
  • Search Sites
  • Web Database Sites

Sounds like a bunch of stuff but, it seems to me that a lot of it is just saying the same thing, in different ways.  In fact, some of the categories share the same templates.

I suppose the categories are such that they speak to different folks for different reasons.  I may choose the Enterprise Wiki from Collaboration Sites but someone else may choose from it from the Content site.  Confused yet?  Don’t worry it will be a while before I get to Publishing sites…in fact, it will be a while before I get to sites in detail.  This is an overview and I’ll keep it at around 30,000 feet.

Blank and Custom Sites

There are two subcategories here, Blank and Personalization Site.

image

Pretty Cool huh?

A blank site is available on any version of SharePoint.  I know, why even make that statement?  Kinda a no-duh movement you say?  Consistency folks.  That’s all.  I will be talking about the three versions and what sites are available.  Three Versions?  Ok, for those new to SharePoint 2010 they are: Foundation (Free), Standard (costs some money) and Enterprise (the most expensive version). 

For many sites and needs, Foundation will suite the situation just fine.  I guess there really is a fourth version that comes with Office – 365 but that is outside of the scope of this document.

Ok, so what exactly happens when you create a blank site?

Creating a blank site

Now, I know that this was an exiting video.  As you can see from the final shot, you have a site with three locations (Libraries, Lists and Discussions) and that’s it.  It’s ready to be set up and configured however you wish.

At this time, we’re going to skip the Personalization site…more on that later.

The next blog snack will go into Collaboration Sites.

Categories: Microsoft, SharePoint Tags: