Why I am excited about SQL Server 2012

By now many of you have had the opportunity to hear or see something about the next release of SQL Server.  It is later on March 7, launch day.  I have enjoyed a number of sessions on the SQL Server Launch site.  Many people have talked about what they look forward to in the product, so I thought I would discuss some of the things that excite me.

image

SQL Server Integration Services Improvements

I think the updates to SSIS make the best case for early adoption of SQL Server 2012.  If you are currently using SSIS or are planning to use SSIS, the changes to the platform are significant and reduce the overall development time for enterprise class solutions.  And yes, developers will see many changes that will further help productivity, “beyond rounded corners.”

ColumnStore Indexes

As I work with large data stores for reporting and loading SSAS, this index type will greatly improve performance.  Once again Microsoft has made significant improvements for large, set based query operations.

Enhanced Window Functions

I have done a lot of work with window functions in Oracle lately.  I am thrilled to see similar functionality being added to SQL Server.  This allows for more complex aggregations and query results while not sacrificing performance.  Join me during the 24 Hours of PASS to see these functions in action.

Power View

Wow.  This visualization tool really fills a gap within in Microsoft’s BI offering.  I am excited to see this in operation at customers as this is a game changer for the end user.  The replay capability is both cool and very functional.

Extended Events for SQL Server Analysis Services

While I have not worked with extended events in SQL Server 2008 as much as I would have liked to, the introduction of extended events for SSAS will allow us to follow the path taken by many SQL Server DBAs who have used these events.  This will allow us to monitor SQL Server more closely while minimizing the impact we experiences using SQL Profiler.

These are just a few items within SQL Server 2012 that excite me, what excites you?

Advertisements

Oracle Tips for MSBI Devs #1: Executing an Oracle Stored Proc with No Parameters in SSIS Execute SQL Task

Originally posted: 3-4-2012

Welcome to a new blog series of tips related to Oracle with a focus on helping those of us who are delivering Microsoft Business Intelligence solutions using Oracle in some way.

The Solution:

In an Execute SQL Task the proper syntax for executing an Oracle stored procedure with no parameters is:

{CALL <schema name>.<stored procedure name>}

image

This works with the OLEDB driver from Oracle. (Do not use the Microsoft Oracle OLEDB driver as it is being deprecated.)

image

The Situation:

The following list highlights the most common errors when trying to get an Oracle procedure to execute.

  • EXEC or EXECUTE – as SQL Server pros we often use EXEC to make the procedure call. When working with Oracle, you need to use CALL
  • Forget the schema name – when using Oracle, you should specify the schema name when calling the procedure. In some cases, you may pick up the schema from the user, but for a consistent pattern and result, you should explicitly specify the schema name.
  • No curly braces – this is the one I did not know and a coworker tracked down some examples of this usage. Enclosing the call in curly braces appears to let the driver know that it is a procedure call and handle it appropriately.
  • Adding a semi-colon – most work with Oracle statements results in a semi-colon to conclude the statement. In this syntax, you will get an error when you conclude your statement with a semi-colon.

Versions:

This tip has been confirmed to work in both SQL Server 2008 R2 Integration Services and SQL Server 2012 Integration Services. The Oracle version tested with is Oracle 11g.

Note on Parameters:

This syntax has not worked consistently with parameters. I will post a followup tip for handling parameters at a later time.

PASSMN Rocks 2012!

Minnesota SQL Server Users Group

As many of you may know, I am the Chair for the Minnesota SQL Server User Group aka PASSMN.  I work with a great group of individuals who have worked hard to get our upcoming speaker schedule and sponsorships worked out.  I am very excited to discuss the upcoming schedule and invite anyone in our area during this time to join us.  So here goes…

Locked In

Tuesday, March 20 – Thomas LaRock

Tom LaRock (@SQLRockStar) is a SQL Server MVP and he is currently a Senior Database Administor for Confio Software.  Check out his blog at http://thomaslarock.com for more information about him and his community contributions.

Topic: TBD

Sponsor: Confio Software

Tuesday, April 17 – Ted Kummert, Mark Souza, and Itzik Ben Gan

Ted Kummert is the corporate vice president of the Business Platform Division at Microsoft which includes SQL Server and SQL Azure.  It is a privilege to have Ted available to speak at our user group and share more about SQL Server.  He was one of the keynote speakers at the PASS Summit in 2011 as well.

Mark Souza joins us this night as well.  Mark is the General Manager for SQL Server at Microsoft.  He is responsible for customer and partner interaction for many products including SQL Server.

Topic: SQL Server 2012

Itzik Ben-Gan is a Mentor and Co-Founder of SolidQ and has been a SQL Server MVP since 1999.  He is the foremost author and speaker on T-SQL related topics.  He is a regular presenter at PASS events including an annual favorite at the PASS Summit.  Check out Itzik’s website to learn more about him: http://www.sql.co.il/index.htm.

Topic: Apply T-SQL Magic

Sponsor: TechFuse Conference

Tuesday, May 15 – Lara Rubbelke

Lara Rubbelke (@SQLGal) is one of our own.  It wasn’t that long ago that she was helping form this very user group. Now she regularly presents on Big Data for Microsoft including during one of the key notes an the 2011 PASS Summit.  Read more from Lara at her blog:  http://sqlblog.com/blogs/lara_rubbelke/default.aspx.

Sponsor: Emergent Networks

Topic: Big Data Architectures

Tentatively Scheduled

Tuesday, June 19 – Jes Borland

Jes Borland (@grrl_geek) joins us from our neighbor state of Wisconsin.  She is an active member of the Wisconsin SQL Server User Group and MADPASS.  She has presented at the 24 hours of PASS and the PASS Summit.  Check out her blog: http://blogs.lessthandot.com/index.php?disp=authdir&author=420.

Tuesday, July 24 – Kalen Delaney

Kalen Delaney may join us in July as she will be teaching a class at Benchmark Learning during that time.  Kalen is the author of Inside SQL Server and one of the original founders of PASS.  She has been a SQL Server MVP since 1993.  You can find out more about Kalen and her upcoming classes at http://sqlserverinternals.com/.

Local Presenter Spotlight

Besides the individuals above, we are also looking forward to hearing from our user community in our monthly, local presenter time slot.  If you are looking for an opportunity to share a new tip or something you have learned about SQL Server – development or administration – please let our program director know.  We want our user group to actively involve users from our own community.  We also would love to grow our speaker ranks from within the community.  We all started our speaking somewhere, let PASSMN be the place to help you learn to present or practice presenting.

If you are interested in presenting at our user group or sponsoring our user group, drop us a line at support@mnssug.org.

PowerPivot and Market Research

This past Tuesday, February 14, I was invited to discuss how technology can be used to help analyze data for the purposes of Market Research at Minnesota School of Business.  A friend of mine, Shane Smith, is teaching this class and invited me to show how the tools I work with every day can help his students analyze data for marketing.

The Setup

The class was analyzing the results from a survey they conducted at the school as a part of a market research project.  The survey had five questions with various types of answers such as Yes/No and multiple choice.

Shane wanted to show the class that using technology, they could glean more results from their survey.  This process would also help them understand the effectiveness of the survey they created.

The Data Prep

For my part, I wanted to introduce them to what I do – business intelligence solution design and implementation.  I also wanted to introduce them to the specific tools which they could use to analyze their results for the project.  In this case, we were using Excel 2010 and PowerPivot for Excel 2010 (SQL Server 2008 R2 version).  The final goal was to help them see that these tools can be used to analyze data in real world scenarios.

Shane provided me with the initial survey results in an Excel spreadsheet.  I took that data and flattened some of it out and turned some of it into data that math could be performed on.  For example, I took a multiple choice survey question with 5 possible values and turned it into 5 columns of data with 1 and 0 as possible values so additional calculations could be done on it.  We also created the equivalent of a dimension by taking one of the multiple choice questions and creating a new sheet with possible values and labels.

The Cool Stuff

I took the sheet we updated the data on and I walked the class through the process of bringing that data into PowerPivot.  This included building a relationship between the dimensional data and the primary survey results which were on two sheets in the source workbook. From there, we generated a couple of pivot table views and some charts.  Next, they came up with other ideas of what data to look at and how they wanted to see it presetned. I walked them through the process of prepping the data as necessary.  Then we created more visualizations and presentations of the data that helped them better analyze their results.

The Wrap Up

Kudos to the Microsoft team that put this toolset together.  These students, who are not technologists, gained confidence in the use  of PowerPivot for their project. They are also now set up to use it in the future for similar projects or even their jobs. Because of its ease of use, it was a great demo and training session on using tools to analyze data.

I had a lot fun sharing the technology that I work in regularly with users who would not have tried to do this on their own.  Tools like PowerPivot enable users to turn their data into usable information on their own.  While there will always be a need to build enterprise solutions, this fills a gap in the marketplace with a tool many are already familiar with.

I hope the students are able to continue to use the tool and find a place for it in their workplace in the future.  If you have not yet given PowerPivot a test run on your own, you can find it here: http://www.microsoft.com/en-us/bi/powerpivot.aspx.  We used the 2008 version, but feel free to give either a test run.  Keep in mind that you need Excel 2010 to use PowerPivot.  Best of luck to all of them and any of you who give it a test run.  Feel free to share what your experience with PowerPivot here in the comments.

PowerPivot for Excel Install Issues

At one of my customers, we had a very frustrating experience while trying to get PowerPivot for Excel (SQL Server 2008 R2) installed on a number of developer machines.  All of the machines were 64-bit running Windows 7. We had Office 2010 32 bit installed in all cases.  The frustration occurred around the error message.

PowerPivot Error Message

We verified we had downloaded the correct version and according to the following Microsoft Support article, http://support.microsoft.com/kb/982765, we also confirmed all of our prerequisites were in place.  After going through this verification process, we had one of the developers completely reinstall MS Office and then install the add-in.  However, we were still unsuccessful.  So, we did what all good developers do and got our local desktop support guy, Steve Schuler, involved.  He was able to successfully work through the issue.

Here are the steps that Steve discovered which allowed us to successfully get the PowerPivot add-in installed.

  1. Save PowerPivot_for_Excel_x86.msi to a local folder.
  1. Right click on Command Prompt and choose Run as Administrator.
  1. Type msiexec/i c:\data\PowerPivot_for_Excel_x86.msi  into the Command window.
  1. Launch Excel and choose Accept when prompted to install PowerPivot.

If PowerPivot does not show in the ribbon, go to File-Options-Add-Ins and select Com Add-In’s from Manage drop-down list and choose Go.  Confirm that PowerPivot for Excel is selected.  If it is not selected, select it and then choose OK.

The root cause of the issue appears to be related to the security policies and administrative privileges on the machines. In our case, we are all local admins and should not have encountered the issue.  We had inconsistent experiences on many machines that were tested.  In some cases there was no issue and then in other cases we could only install with the steps above.

Thanks again to Steve for a job well done.

Thoughts on data, business analytics, and the SQL Server community

%d bloggers like this: