Dan English's BI Blog

Welcome to my random thoughts in regards to Business Intelligence, databases, and other technologies

Posts Tagged ‘sql server’

Tabular Models, Compatibility Level, and Power View

Posted by denglishbi on October 4, 2012

[tweetmeme source=”denglishbi” only_single=”false”]

A couple of weeks ago I did a review of the CTP4 of SP1 that came out for SQL Server 2012 – Installing SQL Server 2012 SP1 CT4 Experience. Part of my test involved upgrading an Excel 2013 PowerPivot file to a Tabular Analysis Service project.  After I did that I setup a BI Semantic Model connection file in SharePoint and explored the Power View designer updates.  It was business as usual and I was seeing all of the new features and functionality that I had previously mention in my posts on Excel 2013 and Power View – Power View meet Microsoft Excel 2013 and Power View meet Microsoft Excel 2013 Part 2.

Well this past week I was prepping for my pre-con for the SQLSaturday #149 event and I noticed something odd.  When I was connecting to my tabular models with Power View I was not seeing my hierarchies or key performance indicators (KPIs).  I was a bit confused.  I upgraded my environment to SP1, so what was going on.  I thought the upgrade provided support for these model features now, right?  I know I had seen them already, maybe I was just imagining things though.  When I was doing my tabular project deployments I was also seeing a new dialog box that I was just clicking ‘Yes’ on without really understanding what it was talking about…


I completed out the pre-con still confused and then last weekend I did a bit more digging into this issue.  I went back to my Excel 2013 SQLSaturday model that I upgraded and I reviewed the property settings on the database.


The item that I noticed that seemed like it might be important was the Compatibility Level setting.  This database was showing SQL Server 2012 SP1 (1103).  I then took a look at the database I was using for some of my demos.


Ahhh, now the light bulb was starting to get bright:) The database that I was deploying was still in the SQL Server 2012 (1100) setting even though I had upgraded my environment to SP1.  Interesting.  Apparently you have the ability to control this setting now if you want to run in different levels or do some testing in the same environment before upgrading all of your databases.


I didn’t realize that I now had to make a change on my model property settings before I deployed my database.

Once this setting is changed you will be prompted with the following:


Once again I click ‘Yes’ and redeploy to upgrade the database on the server.  Now when I connect to my model with Power View I see my hierarchies and KPIs, perfect!


So just something to be aware of now when working with tabular model projects now.  There is a property setting that we have to take a look at, Compatibility Level, and it is a pretty important one for the users of our data models:)

Here are a couple of additional reference links to take a look at (some of this documentation and instructions might differ from what you see with CTP4 of SP1, but they should be available in the RTM of SP1):


Posted in Analysis Services | Tagged: , , | 8 Comments »

Installing SQL Server 2012 SP1 CTP4 Experience

Posted by denglishbi on September 22, 2012

[tweetmeme source=”denglishbi” only_single=”false”]

A standalone version of the SQL Server 2012 SP1 was released this week.  It is not the final release, just a community technology preview (CTP4), but a release that we can test out none the less.  The exciting part of this release to me are the enhancements that have been added to Power View.  Here you will see the steps I went through to upgrade my evaluation virtual machine I have setup that is based on the Appendix B that comes with our book – Visualizing Data with Microsoft Power View.

First you will need to download the SQL Server 2012 SP1 CTP and you can do that from here – SQL Server 2012 SP1 CTP (there is even a full SQL 2012 slipstream version you can download as well that includes the service pack).  You also might want to download some of the additional features that are provided with the feature pack and you can get that from here – Microsoft® SQL Server® 2012 SP1 Community Technology Preview Feature Pack.

Once you have the service pack downloaded you can run this on your machine and follow through the screenshots below.






I had one item that I needed to shutdown so that I could avoid a restart after I installed the service pack and that was related to IIS services (these are running because of SharePoint 2010).


So I had to stop the IIS Admin Service and the World Wide Wed Publishing Service.


I did a refresh check and I was good to go.


Running RTM Evaluation Edition – 11.0.2100.60


Updating the default instance of SQL 2012


Updating 2nd instance of SQL 2012 (POWERPIVOT)


Instance MSSQLSERVER overall summary:
  Final result:                  Passed
  Exit code (Decimal):           0
  Start time:                    2012-09-22 08:15:42
  End time:                      2012-09-22 08:33:33
  Requested action:              Patch

Instance POWERPIVOT overall summary:
  Final result:                  Passed
  Exit code (Decimal):           0
  Start time:                    2012-09-22 08:33:57
  End time:                      2012-09-22 08:35:24
  Requested action:              Patch

Patch Level: 11.1.2845.0


Verify I can connect to my instances of the database engine and Analysis Services along with reviewing the build numbers.

Microsoft SQL Server 2012 – 11.0.2845.0 (X64)
    Sep  1 2012 02:36:04
    Copyright (c) Microsoft Corporation
    Enterprise Evaluation Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Now to go start into SharePoint and check out Power View!


New Ribbon layouts, icons, and features (images, hyperlinks, field list control)


More style, text, and background options


New visualizations – Pie and Map charts along with font sizes


Pie Charts, but no data labels or sorting options.


Bing geo-encoded maps


Pre-defined drill up/down capabilities


Support to restore Excel 2013 PowerPivot files in Tabular SSAS


SQL Saturday #149 is coming up next:)


SharePoint 2010 will not support the Power View that is in Excel 2013 files.


I noticed that my PowerPivot was not working yet, so need to run the PowerPivot Configuration Tool.



New version detected, time to upgrade.


Run the upgrade process.


After this is done we go back in and test out PowerPivot again


Now it works just fine.  The one thing I noticed is that it doesn’t appear to be working with the Excel 2013 PowerPivot files though.


Maybe these will not work in SharePoint 2010, not quite sure yet, will need to look into this more.

For more information on the new features available in the updated version of Power View check out some of these references:

Here are some more links for information on the SP1 updates:

This service pack also includes performance improvements with slicers and filters with PowerPivot and Tabular SSAS as well as I demoed here in this post and video – Excel PowerPivot SQL 2012 vs. Excel 2013 Performance Demo.

Now we just need to wait for the final release and hopefully that will be coming out in a couple of months now, should be very soon. Enjoy!

Posted in SQL Server | Tagged: , | 23 Comments »

Upgrade PowerPivot SharePoint from SQL 2012 RC0 to RTM

Posted by denglishbi on May 21, 2012

[tweetmeme source=”denglishbi” only_single=”false”]

So last week I was doing a presentation on Self-Service BI with SQL Server 2012 at the TechFuse MN technical conference and I realized that my demo virtual machine was still running SQL Server 2012 RC0.  I was thinking that I shouldn’t do my presentation and demos using RC0, so why not upgrade.  Probably not the best idea to do this just a few days out from a presentation, but I had my fingers cross and just went for it.

I found some guidance from a thread in the forums by Jen Underwood here Upgrade in place RC0 to RTM and decided to go for it.  I did the in-place upgrade and my SharePoint virtual machine with SQL 2012 was still functioning after I completed all of the steps.  Then I moved on to testing my integrated SSRS setup and realized that the Power View reports were not working and neither were my data sources.  The quick fix for that was to go into Central Administration and go into the properties of the Reporting Services application and click OK.  That completed the upgrade process for my shared service and my Power View reports functioned properly after that.

Next was to verify my PowerPivot was still working.  I opened up my PowerPivot files and uploaded them to SharePoint.  The snapshots appeared in the PowerPivot Gallery just fine, but I was unable to work with the files (the slicers did not work, I received error messages as shown below).  I also went into Central Administration and tried to checkout the PowerPivot management dashboard.  In doing this I received the same error message below:


An error occurred during an attempt to establish a connection to the external data source. The following connection failed to refresh: PowerPivot Data

I wasn’t quite sure how to proceed, but after the fix for the SSRS shared service I was thinking that maybe I needed to do the same for the PowerPivot application.  SO I decided that I would try to re-run the PowerPivot configuration tool which is new with the SQL Server 2012 installation.PowerPivot Configuration Tool

The following are the screenshots of running the PowerPivot Configuration Tool with the SQL Server 2012 RTM installation software against my virtual machine that was RC0:








Once that was done I went back into Central Administration and checked out the PowerPivot Management Dashboard again:


Everything was working as expected again and no more error messages! Victory!

That was quite the relief and I was able to do my presentation with my newly upgraded RTM release of SQL Server 2012, not bad for doing an in-place upgrade just a few days prior to the big event.  This included three instances of SSAS (multidimensional, tabular, and PowerPivot), the SQL database engine, SSRS, SSRS SharePoint integrated, SSIS, and DQS as well.

Here is some additional information if you are looking to do the upgrade for DQS – Upgrade DQS: SQL Server 2012 RC0 to SQL Server 2012 RTM.

Posted in PowerPivot, SharePoint | Tagged: , , | Leave a Comment »

SQL Server 2012 RTM is here!

Posted by denglishbi on March 7, 2012

[tweetmeme source=”denglishbi” only_single=”false”]

Yesterday I was at the Minneapolis Microsoft Technology Center and I received the news short after 11 a.m. that SQL Server 2012 went RTM and the evaluation version is available to download.sql2012

The SQL Server 2012 Virtual Launch is going on today – http://sqlserverlaunch.com, so don’t miss out on that. I heard that over 20,000 people are registered, so this is going to be one of the largest online launch events.

You can grab the downloads here – http://www.microsoft.com/sqlserver/en/us/get-sql-server/try-it.aspx.

On this site you will find the links for:

To find out what has changed in the new version of SQL Server 2012 checkout the online material available here – What’s new in Microsoft SQL Server 2012.

Also, don’t forget that the SQL Server 2012 Special Ops Tour is going on as well.  The one for Minneapolis still has seats available.  Make sure you register for that today as well – http://specialopstour.com.


Posted in SQL Server | Tagged: | Leave a Comment »

SQL Server 2012 Upcoming Launch Events

Posted by denglishbi on January 30, 2012

[tweetmeme source=”denglishbi” only_single=”false”]

Just wanted to do a quick post to make sure everyone was aware that there are a few SQL Server 2012 Launch events coming up in the near future.  sql server 2012

SQL Server 2012 Virtual Launch Event

The first one is on Wednesday, March 7.  This is a free event and it is also an online virtual event as well.  There will be a couple of keynote presentations and then three tracks and it will even include a virtual ‘Expo Hall’ where you can talk with vendors and partners as well.  This will be a great event to get exposed to what is coming in SQL Server 2012 so you will be aware of the new features and capabilities.  Register now over at http://sqlserverlaunch.com/.

SQL Server 2012 Special Ops Tour

Another event that will be starting up in March is the SQL Server Special Ops Tour being put on by the PASS organization and Microsoft.  This will be in the U.S. only and going to 12 cities – Atlanta, Irvine, Denver, Columbus, Jacksonville, Chicago, Dallas, Philadelphia, New York, Boston, Minneapolis, and San Francisco.  Now this is not a virtual event, so seating will be limited.  Registration is open and it is a free event, so make sure you secure your spot today – http://specialops.sqlpass.org/#fbid=PH48KYb0Gig.

24 Hours of PASS – Spring 2012

Another 24 back-to-back hours of free SQL Server training is also coming up brought to by the PASS organization and the SQL community.  Call for speakers is currently open through tomorrow and the event will take place on March 21, focusing on SQL Server 2012 of course.  For more details check out the following link – http://www.sqlpass.org/24hours/spring2012/.

SQL Server Connections – Spring 2012

If you are up for a conference and want to head to Las Vegas, NV, then you are in luck.  The Spring 2012 DevConnections conference is coming up and there will be a SQL Server 2012 Launch Conference and Expo included provided by Microsoft and SQL Server Connections.  This event takes place March 26-29.  For more information check out the details here – http://www.devconnections.com/shows/sp2012/default.aspx?s=185.

As you can probably tell the release date for SQL Server 2012 is coming up.  Lots of excitement and information is just pouring out and looking forward to the official release of the product.

Posted in SQL Server, Training | Tagged: , , | 1 Comment »

MN SQL Saturday #99 Event Update

Posted by denglishbi on November 8, 2011

[tweetmeme source=”denglishbi” only_single=”false”]

The big day is almost here, just three more days and then it is time for the Minnesota SQL Saturday #99 event at Grace church in Eden Prairie (maps and directions).  If you are currently signed up but are unable to attend, please go to the registration site to update your status.  There is a waiting list going right now and we are actively reviewing that throughout the week.

Now for some of the details:sql sat 99 logo

  • There have been some schedule changes and a few updates, so please review the online schedule.  The room numbers have been posted for each track as well on the site in the track names.  You can also build your schedule list online – http://sqlsaturday.com/99/schedulebuilder.aspx (login to the site is required).
    • Some things to note:
      • We have two lunch sessions thanks to our You Rock sponsors Quest Software and HP-Microsoft Alliance; Baselining for SQL Server and Converged SQL Server Solutions from HP & Microsoft
      • WIT Panel Discussion has been set and Nicole Roepke, Amanda Bates, and Barb Rokke will be leading the discussion. Tim Plas will be moderating this session.
      • In the DBA2 Track The Powershell and Perfmon and Database Restores sessions have traded time slots
      • In the BI1 Track Intro to Data Mining and Inside PerformancePoint sessions have traded time slots
      • In the Prof Develop Track the Entity Strategies: Structuring your Consultancy has move to the 2 p.m. slot, two new sessions have been added as well – SQL Community & Social Networking (MVP Ted Krueger will be moderating this discussion as well) and Geographic Visualizations using Maps in SSRS
      • In BI2 Track Becoming DAX and Securing an SSAS, SSRS, and SharePoint BI Solution sessions have traded time slots
      • A new session was added to DBA2 track – PowerShell for the Reluctant DBA
  • The doors will open up at around 7:30 so you can start to get registered.  When you get to Grace church come in on the east side of the building (turn on Spring Rd and enter parking lot) and the door you will enter in through is labeled #4.  Everything will take place upstairs, so once you are checked in proceed down the hallway and take the stairs to the terrace level.
  • There is a Guidebook App available that you can put on your mobile device or check out online as well – http://m.guidebookapp.com/697/
  • There will be wifi access available at Grace church

That is all for now, might do one more update Thursday, otherwise I will see you there if you are planning on attending!

Posted in SQL Server, Training | Tagged: , , , | 1 Comment »

Microsoft Business Intelligence (BI) Resources

Posted by denglishbi on October 13, 2011

[tweetmeme source=”denglishbi” only_single=”false”]

UPDATE (10/20/2011): Added an updated link for the PowerPivot TechCenter – http://technet.microsoft.com/en-us/bi/ff604673.aspx.

Attended a session today on Power View and received a USB drive with some good resources on it. I thought I would share one that contained a bunch of links:


Microsoft BI Resources

PowerPivot Hands on Demo and Download: http://powerpivot.com
PowerPivot (Self-Service BI):
SSRS (Operational & Managed Reporting):
SSRS Report Builder (Ad Hoc Reporting):
PerformancePoint Services (Dashboards, Scorecards, Advanced Analytic Charts & Grids):
Excel (Personal Productivity):
SSAS (Data Mining):
Forums to use for researching / posting questions

I also have a list of the Microsoft BI portals, but I think most if not all are possibly listed above here already – Microsoft Business Intelligence Portal Page References.

Posted in Business Intelligence | Tagged: , , | 6 Comments »

SQL Server ‘Denali’ is 2012, ‘Crescent’ is Power View

Posted by denglishbi on October 12, 2011

[tweetmeme source=”denglishbi” only_single=”false”]

The name is officially set and the next version of SQL Server (SQL11) will be 2012 and the release date will be in the first half of next year.  There will be a Release Candidate (RC0) should be provided by the end of the year that will be feature complete that everyone will be able to download and test out to provide feedback.  This is very important to do so that you can report any bugs or items to the product team to review prior to the Release to Manufacture (RTM).sql server logo

Along with this the new self-service reporting tool (code named ‘Crescent’) has also received an official name of Power View.  PowerPivot provides the self-service data modeling capabilities to integrate your data to create a BI Semantic Model (BISM) and Power View provides the highly interactive data exploration tool.  One of the new features that has been added since CTP3 is the ability to have multiple views of the data within an existing rdlx file.  This reminds me of the briefing book concept that ProClarity has where you would create and save multiple analytical views.

Stay tuned for more information and postings from the community as the PASS Summit event continues through the week.  You can stay up-to-date by checking out the #sqlpass hash tag on Twitter.  If you are not at the conference you can also checkout the keynote events that are being streamed each day and recorded on the event site – PASS 2011 Summit Live Streaming.

Posted in SQL Server | Tagged: , , | Leave a Comment »

Minnesota SQL Saturday 99 Schedule Posted

Posted by denglishbi on October 6, 2011

[tweetmeme source=”denglishbi” only_single=”false”]

The schedule has been posted for the upcoming SQL Saturday event in Minnesota that will take place on Friday, November 11.  There were a lot of sessions submitted and it is always tough to pick the final lineup of topics and I think we have a great mix of topics that will be of interest to everyone from business intelligence, database administration, SQL development, and even a professional track.sql sat 99 logo

Time BI 1 BI 2 DBA 1 DBA 2 DEV 1 DEV 2 PRO
8:15 Minimal Processing for Large Cubes Analysis Services vs. PowerPivot Denali A Beginners Guide to Execution Plans Database Restores – Lessons from the Real World Beyond Rounded Corners – SSIS Changes in Denali Diving into the Cloud with SQL Azure Women in Technology Panel Discussion
9:45 Inside PerformancePoint Introduction to Data Warehousing High-Availability and DR Options for SQL Server Intro to Locking and Blocking Custom Assemblies for Reporting Services SQL Server Service Broker – An Overview Entity Strategies Structuring Your Consultancy
11:00 Lunch Lunch Lunch Lunch Lunch Lunch Lunch
12:30 SQL Denali – What’s new for PowerPivot and SSAS! Becoming DAX: An Introduction Performance, Scalability and Efficiency Eating the Elephant: SQL Server Table Partitioning TSQL Tips and Tricks SSRS Multi-language & Dynamic DB Connections Do SQL Server Certifications Really Matter?
2:00 Introduction to Project Crescent Uniting Free Government Data using PowerPivot Storage Desgin and SQL Server Performance Merge Replication for Offline Data Mobility Building a SQL Listener Job using PowerShell Introduction to Integration Services Sustainable (a Green) IT: A Different View
3:30 Introduction to Data Mining in SQL Server Securing an SSAS, SSRS, and SharePoint BI Solution Don’t Panic, DBAs! Databases on VMware mad easy. PowerShell and Perfmon – A performance story Loading a Data Warehouse with SSIS SQL Denali – An Open Window To T-SQL Enhancements Building a Data Professional Toolbox for Free
4:45 Wrap-up & prizes Wrap-up & prizes Wrap-up & prizes Wrap-up & prizes Wrap-up & prizes Wrap-up & prizes Wrap-up & prizes


Online Schedule –> MN SQL Saturday #99 Schedule (includes speakers names and links to sessions descriptions)

The great thing about this is that everyone that submitted a session is getting a chance to speak (at least I don’t think anyone was left out). So what do you need to do now that the stage is set? REGISTER!

Yes, now that we have the location set, Grace Church in Eden Prairie (very large facility and will be a great place for the event), the schedule is set, and our sponsors are lined up, now we need everyone to complete the registration process.

Register here –> MN SQL Saturday #99 Registration

We already have over 200 people registered and we have the max capacity set at 350, so if you want to secure your spot for the event register now.  Once the spots are filled a waiting list will be started, but we can’t guarantee you a spot, so make sure you get in before we reach our cutoff.

P.S. There will be a continental breakfast provided as well and stay tuned for more updates and news to follow as the event date gets closer.  It is going to be a great one! (#99)

Posted in SQL Server, Training | Tagged: , , , | Leave a Comment »

MN Microsoft BI User Group 2011 Q3 Follow up

Posted by denglishbi on September 20, 2011

[tweetmeme source=”denglishbi” only_single=”false”]

UPDATE (9/23/2011): A link to the Project “Crescent” Demo video has now been added and featured on the “Crescent” wiki page in the Videos section – Project Crescent Overview.

Last week I presented on What’s New with BI in SQL Server Denali (SQL11) at the Minnesota BI User Group Q3 meeting with Jin Cho from Microsoft.  Granted that in only an hour and a half we couldn’t do the topic complete justice because there is just so much to cover and it helps to provide some demos to showcase the new features and enhancements that have been made.  The topics that we covered where going over Analysis Services and PowerPivot, talked about the BI Semantic Model, Columnstore Indexes, Integration Services, Data Quality Services, Reporting Services SharePoint Integration, and “Crescent”.

I wish I would have had more time to be able to demo some of the new SSIS items, but I allocated my demo slot to show the new self-service reporting authoring tool Crescent.  Each of the topics that we did talk about can easily be their own topic for presentation, so stay tuned for the upcoming MN SQL Saturday event and the next MN Microsoft BI User Group Q4 meeting in December.  There will also be a lot of content in regards to this topic at the upcoming PASS 2011 Summit next month that I would highly recommend.

You can download the presentation from my SkyDrive account or you can check out the slides available via SlideShare as well:


I have also setup a video that goes over the “Crescent” demo that I went over during the presentation.  I believe that when I switched laptops during the presentation that I did not share the desktop with Live Meeting, so I apologize for that and since this will not be part of the recording I put together a quick video.

Project “Crescent” Demo from Dan English on Vimeo.

A link to the Project “Crescent” Demo video has now been added and featured on the “Crescent” wiki page in the Videos section – Project Crescent Overview

For more BI highlights in the upcoming SQL Server release check out this list – Top 10 BI Reasons to Upgrade to SQL Server ‘Denali’ (SQL11).

Posted in Business Intelligence, SQL Server | Tagged: , , , , , , , | Leave a Comment »


Get every new post delivered to your Inbox.

Join 1,993 other followers