Wednesday, September 3, 2014

Accidental SharePoint Designer 101

I fully profess to know little to nothing about SharePoint, but I occasionally get pulled into setting up little sites or adding web parts for some of my reporting and business intelligence work.  Each time, I have to relearn the start-up steps to create what is needed!  So I decided to record a few of my go-to places so I can remember next time.  I used SharePoint 2010 to document the below steps, but the directions may be applicable to other versions.  Also, these steps assume you have full control of your site.

Getting Started

The first step is start editing the page rather than looking at it like an end user.  Do this by:

  1. Select the Page tab at the top of the screen
  2. Click the Edit Page button/drop down list
  3. Select the Edit Page option

PS. When you're done, do these same steps, except select the "Stop Editing" button.

Content Creation

You may need to create a document library, a list, or another type of container.  I like to create my content first because it makes the design portion later. To do this:

  1. On the Site Actions menu in the top left corner, select the appropriate content creation option:
    • New Document Library
    • New Site
    • More Options
  2. Specify the important info, and be sure you're happy with the view of everything

Web Part Addition

As long as you're in editing mode, this is straightforward: Just select the Add a Web Part link where you want your web part.

PS. this is where creating your content comes in handy because you can select your content without having to pick the web part type.

Web Part Editing

To make any changes to the web part, such as style, name, or option, you can do that in the page itself.  To do this:

  1. In editing mode, hover your mouse over the web part you want to modify
  2. On the right side of the toolbar (at the top of the web part, next to the title), click the down arrow
  3. Click the Edit Web Part menu option
  4. On the right side of the screen, make any setting changes you would like, and be sure to select the OK button at the bottom.

Color and Style

Next, we want to make the color and style to match either the rest of the system or to match our own colors! Change this by:

  1. On the Site Actions menu in the top left corner, select the Site Settings option
  2. Under the Look and Feel section, click the Site theme link
  3. Either inherit your parent's theme, specify your own theme, or customize your color options

Left Navigation Menu

My usual goal is to make the page look less "SharePoint-y", which includes removing the items from the left navigation menu and making links to other sites or pages within the site.  You can change the menu by:

  1. On the Site Actions menu in the top left corner, select the Site Settings option
  2. Under the Look and Feel section, click the Navigation link
  3. Scroll down to Navigation Editing and Sorting and have fun playing!

Security

Hopefully, someone is going to use the page that you just put so much time and energy into!  So we need to give those people access.  Do this by:

  1. On the Site Actions menu in the top left corner, select the Site Permissions option
  2. Give access to either Windows or SharePoint groups and decide what permission they get

PS. It is important have a good security plan in place, and hopefully you can work with your SharePoint administrator on this.

Good luck if you end up an "accidental SharePoint designer" like me!

Tuesday, July 29, 2014

Upgrading your SSIS Management Framework: Part 3

At this point, you understand the options for moving an SSIS framework to the latest version of SSIS, and you've upgraded the logging portion of the framework using a hybrid approach.  The final step in the framework upgrade is handling your configurations.  Let's walk through an existing configuration implementation and how you can upgrade it by combining your existing implementation with the standard SSIS framework.

Overview

A typical "old-school" configuration scheme is described in the SSIS PDS book or in this blog post here: http://jessicammoss.blogspot.com/2008/05/ssis-configuration-to-configuration-to.html.  Starting in SSIS 2012, the configuration scheme uses environments and parameters when using the Project Deployment Model, as discussed here: http://msdn.microsoft.com/en-us/library/hh213290(v=sql.110).aspx.

In both scenarios, the core ideas in a configuration scheme are:

  1. Provide the ability to move packages through environments without having to touch the packages
  2. Provide one location where connection strings / variables are stored, so in case a value changes, you don't have to change the value in multiple places

Assumptions

To enable our hybrid approach, we will utilize the SSIS 2012+ catalog as our "master" version of the configuration values and modify the previous framework to use its data.  This example counts on the following assumptions.  If your system is different, you may need to make some modifications to the implementation described here.

  1. Assumption for the old framework: All configuration entries modify Variables, rather than Connections or other object types.
  2. Assumption for the new framework: You have a CommonConfigurations environment on each server that holds your values for your development, test, and production servers, as well as an environment for each package that would have its own package level values.

Configurations

To tie the two systems together, we will use the environments stored in the 2012+ catalog to pass to the earlier framework.  The earlier framework retrieves all of its information from a table called dbo.SSIS Configurations, so we can just replace that table with a view that points to the new catalog!

Start by renaming your old table to [SSIS Configurations Old]:

EXEC sp_rename 'dbo.SSIS Configurations', 'SSIS Configurations Old';
GO


Next, create a view named [SSIS Configurations] that reads from the SSIS catalog:



CREATE VIEW dbo.[SSIS Configurations]
AS
    SELECT CAST(e.name AS NVARCHAR(255)) as ConfigurationFilter
        , CAST(ev.value AS NVARCHAR(255)) AS ConfiguredValue
        , CAST('\Package.Variables[User::' + ev.name + '].Properties[Value]'
                AS NVARCHAR(255)) AS PackagePath
        , CAST(ev.type AS NVARCHAR(20)) AS ConfiguredValueType
    FROM [SSISDB].[catalog].[environment_variables] ev
    LEFT JOIN [SSISDB].[catalog].[environments] e
        ON ev.environment_id=e.environment_id
GO


Compare the output from SSIS Configurations and SSIS Configurations Old, and add any additional variables needed to the environment in the SSIS catalog.  From now on, you will add new configurations only to the new catalog.  You have one place to keep the "master" values and you only change them in one place!



Good luck!



Keep in mind that based on your framework implementation, not all of this may be applicable.  However, I hope that it gives you something to think about as you are evaluating your framework upgrade options!

Friday, June 27, 2014

Upgrading your SSIS Management Framework: Part 2

Based on Part 1 of Upgrading your SSIS Management Framework, you’ve decided to go with a hybrid approach for your framework.  The hybrid approach which will use some components of the custom framework (this post will use the framework provided in SSIS PDS, but the concepts are applicable to any custom framework) and also utilize the standard SSIS framework.  This allows you to tie your existing package ecosystem with the latest and greatest built-in framework. Let’s talk through an overview of what we’re going to do and then explain each of the steps needed to implement it.

Overview

When it comes down to it, we need to accomplish two main things for this hybrid approach: tie our logging tables together and tie our configuration tables together.  When it comes to logging, each system has its own important identifier (ID) that can get you to anything else in the system.  The important ID in the custom SSIS framework is the PackageLogID, and the important ID in the standard SSIS framework is the ServerExecutionID.  The work you need to do is to map these two executions together.  When it comes to configurations, you want to be able to have one place to modify your connection strings and common variables that will modify all of your packages.

Logging

Let’s start with logging.  As previously mentioned, our goal here is to the two logging systems together.  Since we don’t want to modify the standard SSIS framework (that would defeat the purpose of moving to that framework!), we’ll do our modifications in the existing framework.  However, we want to be sure not to change anything in the packages themselves because that would cause a lot of rework.  Fortunately, we can do this in the table and stored procedure that the framework utilizes.

Begin by adding a new column to your main table that contains package executions, such as:

IF NOT EXISTS(SELECT * FROM sys.columns WHERE [name] = N'ServerExecutionID' AND [object_id] = OBJECT_ID(N'PackageLog'))
BEGIN
    ALTER TABLE [dbo].[PackageLog]
    ADD [ServerExecutionID] bigint NULL
END


Next, you will modify the stored procedure to populate the field you just added:



UPDATE dbo.PackageLog
SET ServerExecutionId = (
    SELECT MAX(execution_id) AS execution_id
    FROM SSISDB.catalog.executions
    WHERE status = 2 AND end_time IS NULL
        and package_name = @PackageName )
WHERE @PackageLogID = @PackageLogID


This will insert the ServerExecutionID from the standard framework into the custom framework.  Next, you can modify your reporting queries to utilize the new column.  For example, I modified the existing standard framework query for the “All Executions” report to include information from the old framework in the following query:



SELECT TOP(10)
    a.[execution_id],
    CAST(a.[start_time] AS smalldatetime) AS shortStartTime,
    CONVERT(FLOAT, DATEDIFF(millisecond, a.[start_time], ISNULL(a.[end_time], SYSDATETIMEOFFSET())))/1000 AS duration
FROM [catalog].[executions] a
INNER JOIN [catalog].[executions] b ON
    a.[package_name] = b.[package_name] AND
    a.[project_name] = b.[project_name] AND
    a.[folder_name] = b.[folder_name]
WHERE b.[execution_id] = ? AND
    a.[status] = 7
UNION ALL
SELECT a.PackageLogID,
    CAST(a.[StartDateTime] AS smalldatetime) AS shortStartTime,
    CONVERT(FLOAT, DATEDIFF(millisecond, a.[StartDateTime], ISNULL(a.EndDateTime, SYSDATETIMEOFFSET())))/1000 AS duration
FROM SSIS_PDS.dbo.PackageLog a
INNER JOIN SSIS_PDS.dbo.PackageVersion b ON a.PackageVersionID=b.PackageVersionID
INNER JOIN SSIS_PDS.dbo.PackageVersion c ON b.PackageID=c.PackageID
INNER JOIN SSIS_PDS.dbo.PackageLog d ON c.PackageVersionID=d.PackageVersionID
WHERE d.ServerExecutionID = ?
     AND a.ServerExecutionID IS NULL
ORDER BY [start_time] DESC


In this way, you can see all executions together, and you can use similar queries to tie any executions from the old framework to the new one!



Configurations



Next week, we’ll look at configurations and how to manage those in both frameworks.

Tuesday, June 17, 2014

Upgrading your SSIS Management Framework: Part 1

Background

Before SQL Server 2012, SQL Server Integration Services (SSIS) had no built-in logging, auditing, and configuration framework.  All of the pieces were available to build your own, but everyone ended up doing that just a little bit differently.  Most of us consultants came up with our own variation to implement at client sites and ensure that all of those functions at that one client were the same.  I'm especially proud of the framework that Rushabh Mehta and I developed that is published in Microsoft SQL Server Integration Services: Problem, Design, Solution (SSIS PDS) and implemented by many others as well.

Along came SSIS 2012, when Microsoft realized this "multiple-different frameworks" spread was happening, and thought "how cool would it be if we could standardize the framework so ALL our clients have the same one".  This would not only reduce initial development time of the framework, but also ensure that upgrades and future maintenance would go smoothly. Coinciding with (or perhaps due to) this decision, Microsoft moved the execution of SSIS packages to run inside of SQL Server.  Having a consistent framework is wonderful, and I'm a big fan of using the consistent built-in framework.

Upgrade Options

However, what do you do if you've already implemented a custom framework, similar to one in SSIS PDS?  There are a couple of options:

  1. Move lock, stock, and barrel to the new framework. To do this, you would have to upgrade all existing packages, remove the components that logged to the framework, and change your configurations schema to use parameters.  This means that you would never have to worry about upgrading your framework again because Microsoft will take care of it.  On the other hand, you would lose ties to your existing log records.  If you have a small number of packages or you have only used a custom framework for a short amount of time, I would recommend this option.
  2. Stick with what you've got. This option is the most easy to implement, but provides the least amount of value.  The new SSIS framework contains much more logging than most of the custom frameworks, and when a package fails and you don't know why, mo' logging = mo' better.  I do not recommend this option.
  3. Use a hybrid approach, which the potential to phase out the custom framework in the future.  This option includes a little bit of up-front work, but will be maintainable (and hopefully enhanced!) in future SSIS versions.

Okay, let’s do it!

Next week, we'll look at how to implement the hybrid approach based on the PDS framework.

Tuesday, June 10, 2014

Where is my BI Development Tool?

DontPanic

"The development tool for SQL Server business intelligence packages is missing!" says almost every developer who installs SQL Server 2014. It's okay, don't panic.  Microsoft decided to separate the installer for the server and the installer for the development environment.  Unfortunately, the next statement by almost every developer who installs SQL Server 2014 is "There are MULTIPLE SQL Server Data Tools?!?".  Also not a problem, let's talk about the difference options and how to get the correct software on your machine.

Option 1 is SQL Server Data Tools (the original), which is a template for Visual Studio that allows you to create and store information on databases and database objects.  This tool is part of Visual Studio.  For more information, see: http://msdn.microsoft.com/en-us/data/hh297027.

Option 2/3 are SQL Server Data Tools - Business Intelligence, which contain the templates for SSIS, SSAS, and SSRS (the standard BI tools we are accustomed to). This tool is part of SQL Server.  You have two places to download this (depending on if you're developing for SSIS 2012 or SSIS 2014.  Here are those download links:

  1. Microsoft SQL Server Data Tools - Business Intelligence for Visual Studio 2012 (and SSIS 2012): http://www.microsoft.com/en-us/download/details.aspx?id=36843
  2. Microsoft SQL Server Data Tools - Business Intelligence for Visual Studio 2013 (and SSIS 2014): http://www.microsoft.com/en-us/download/details.aspx?id=42313

I do wonder why they are named the same.  Is it possible that Microsoft might merge these two products in the future?  This could be a great move for full data warehouse projects - designing your data layer and then populating it with ETL go hand-in-hand!  Pretty please?

Tuesday, June 3, 2014

Eek! Starting as a Regular Full-Time Employee

Well, I jumped into the career deep end last week.  I started my first-ever job as a regular full-time employee.  This week contained a mixture of emotions for me: scared, nervous, but overall - EXCITEMENT!  It's been a long road, but I'm very happy with where I've come and where I'm going.

I started with a consulting company right out of college.  Back then, graduating with a computer science degree pretty much meant you knew a little bit about a lot, but had no deep knowledge in any area.  So working on a little bit of everything to gain some knowledge in a consulting company seemed like a perfect fit!  I still remember during my interview, the interviewee said "You'll be assigned a project over the weekend, buy a book on the technology, and by the time you arrive on Monday, you need to be an expert".  While we all know that won't exactly work, I was able to learn SO much by hopping around and trying different technologies, industries, and systems.

While trying out different technologies, I fell in love with data warehousing.  To that end, I then tried my hand at my own business, still consulting, but with more training and mentoring.  I absolutely loved this kind of work, but wasn't a huge fan of the full-time travel or all of the paperwork ;)  I then went back to consulting for a couple of local companies, where I was able to work with many organizations with data, without the travel or paperwork.

So... after ten years of consulting, why did I give it up?  Truthfully, I'm ready for a change.  While it's fun to bounce from organization to organization, there's always that dichotomy between "the client" and "the consultant".  And I don't blame either side for thinking that way.  Consultants provide a great service for organizations that don't have the resources (time, people, money, etc.) to do it themselves.  At the end of the day, a consultant's number one duty is to their company, whether that is looking for additional sales or is eventually leaving the project to move onto the next.

All that being said, I'm off to start my new adventure!  I'm a data architect for a firm based in Richmond that handles chemicals and manufacturing.  I get to help them consolidate their data in a data warehouse and hopefully learn a bunch in the process... I also need to learn how to be a regular full-time employee.  Do you have any advice for me?

Thursday, January 3, 2013

Happy New Year Resolutions

imageHappy 2013! 2012 was a great year, and I hope that 2013 will be even better. To assist, I decided to create resolutions this year.  I don't usually create resolutions because I believe that they aren't maintainable.  Too often we hear of the gym membership that expired after a month due to disuse, the 20 pounds to lose that became 2 pounds, the bad habit that we were going to break... tomorrow…

But what I do like about new year resolutions is that the new year is a fresh start, a way to wipe the slate clean and wish for a better and brighter future.  To make sure my resolutions don't become those drift into the ether, I need specific tasks that will force me to accomplish this.

So with that optimistic outlook, my professional resolutions for 2013:

  • Blog twice a month. This will be my hardest resolution based on past history. ;)
  • Tasks:
    • Tell people I'm going to do this (DONE!)
    • Create an initial list of blog topics and dates - is there anything you want to read about?
    • Add task reminders to my RememberTheMilk list so that I don't forget
  • Learn more about PowerPivot, BISM, and DAX.
  • Tasks:
  • Learn more about Windows Azure SQL Reporting.  I haven't delved into the exciting world of Microsoft cloud options, and I want to be able to intelligently share the different options available to people.
  • Tasks:
    • Set up my own account to explore the options
    • Create a comparison chart of pros and cons for cloud versus traditional BI/reporting options
  • Explore EIM  integration of DQS, MDS, SSIS. Matt Masson delivers a great presentation that shows how to use these three products together to create an EIM infrastructure.  I believe the real power of these tools include using each product to create a full data ecosystem.
  • Tasks:
    • Find a realistic example and implement a solution
  • Employee Management Training. On the "soft skills" side, I need to learn more about management.  I've recently agreed to start taking on minions--er, I mean managing employees.  This is an entirely new skill for me.  Since it directly affects the development and career of others, I really don't want to screw it up.
  • Tasks:
    • Harvard ManageMentor training.  I am lucky to be part of a company that believes in training its employees by offering opportunities such as this.  I plan on utilizing this service this year
    • Read The First-Time Manager

I think that's enough to start 2013 off right.  What are your resolutions?
Jessica