PowerPivot was a great new tool when it released several years ago. Now the newest version is out along side SQL Server 2012.You can still install the 2008 R2 version of PowerPivot or you can try out the new 2012 RTM version. There are some nifty improvements that Devin Knight has chronicled ( Blog | Twitter ). There are several requirements that are not part of the 2008 R2 requirements. The install instructions are the following with the first step being the new requirements: Install .NET Frame...
Read More
Between SharePoint 2007 and SharePoint 2010 there were many new features that were added or enhanced. There still remains a list of items that are either unsupported actions, such as workbook authoring, or unsupported features, such as cell comments. In Excel Services 2007 many unsupported features would cause the workbook to not load at all and have since had the behavior changed in 2010. The following is a list of features that would prevent the workbook from loading in 2007. They are still un...
Like all systems built on top of SQL Server SharePoint 2010 needs to have a plan for availability. There are many methods for configuring high availability, one of which is database mirroring. Other options, such as clustering will be discussed separately. Database mirroring is a SQL Server technology used for providing database redundancy. It is configured on a database level, not the server level. With this method any transaction on the the principal server are sent to the mirror server/databa...
In T-SQL there are many functions there to help do a variety of different things. SSIS is no different. In fact, a good number of the commonly used functions from T-SQL are in SSIS as well. Here we will explore the different Trim functions found in SSIS. We will be using the test phrase " Test " to illustrate the three functions: LTRIM, RTRIM and TRIM. LTRIM (Left Trim): Just as in T-SQL, this removed empty spaces from the beginning (or left) of a string. Notice the highlighted result that the t...
In an SSIS package you have the option to choose from a wide range of source from Flat Files to writing queries using OLEDB sources such as SQL Server. Often times, such as writing queries against SQL Server, the metadata will come from the table and you won't need to worry about truncation in the source. Flat files are scanned and metadata is chosen based on the file so those are usually good to go as well. Excel however tends to just use the default length on all columns of 255 characters. Thi...
There are several ways to check the version of SharePoint 2010 currently installed in your environment. That about sums up what we are talking about here. Check it out below. Method 1: Through SharePoint 2010 Central Administration: Open SharePoint 2010 Central Administration Click on the link for System Settings Click the link for Manage Servers in This Farm Look at the Configuration Database Version field Method 2: A detailed list through SharePoint 2010 Central Administration: Open SharePoint...
With SQL Server 2012 right around the corner and Release Candidate 0 already out there I'm sure you will all want to see the cool new stuff that SharePoint has to offer. There are a number of changes coming to SharePoint with the SQL 2012 release including the much anticipated PowerView . A number of enhancements have been made to the installation process as well as the consolidation of Reporting Services for SharePoint integration; which is now all handled through Central Administration rather ...
You may fire up the SharePoint 2010 Management Shell (which allows you to run PowerShell commands to do cool SharePoint stuff, very technical… I know) and get the following error: The local farm is not accessible. Cmdlets with FeatureDependencyId are not registered. I have found there are two places you may get this error: The first is kind of a “duh” moment if you will: not running the Management Shell as an administrator will produce this error every time if UAC is turned on...
Restoring a SharePoint site collection can be a pretty easy task. If your interested in how to do that check this blog ( Backup and Restore SharePoint 2010 Site Collection With PowerShell ) where I explain the process. Unfortunately this is not always a foolproof process as I have recently found out. Most errors are pretty self explanatory such as the site collection already exists, in which case change use the -Force command to overwrite it. I recently came across the following error: Restore-S...
If you are like me then you like to reinstall Windows on your laptop every few months to keep things fresh and running smoothly. That or your even more like me and you download some beta version of software and install it on your laptop rather than a VM and screw something up to an unrepairable state. Either way Windows is being reinstalled. I have a laptop without an optical drive in it. The vast majority of the time I don’t mind as I pretty much use ISO files exclusively. This is a probl...
While some people may consider this topic off limits the fact remains that more and more companies are adopting Apple devices, namely the iPad, for use in their organizations. In the last quarterly earnings call from Apple they revealed that more than 80% of fortune 100 companies and more than 60% of fortune 500 companies are now using iOS devices in their organizations. Regardless of the amount of implementation behind those numbers we are going to have to plan our reporting around the use of p...
If you have used SSIS to move data from something like Access or more commonly Excel, it is a good possibility you have seen this error at some point: Error: The ‘Microsoft.Ace.OLEDB.12.0 ‘ provider is not registered on the local machine. The reason this error shows up is because the proper components are not present that are required to move data from Office files (I’m looking at you Access and Excel). If you do development on a laptop or desktop then there is a very good chan...
PowerPivot report returns the following error from Excel Services: The data connection uses Windows Authentication and user credentials could not be delegated. The following connection failed to refresh: PowerPivot Data When viewing a PowerPivot workbook in SharePoint 2010 the report is visualized using Excel Services since ultimately the report is inside an Excel workbook. The root cause of this error is that Excel Services is unable to get a valid Windows account from whoever is running the re...
If you have ever closed a query window by accident only to wish you could get back your work an instant later you may be in luck. If you ran the query in question there is hope for you. The query below will return queries that have been run for you including the time and query text. I haven’t figured up a way to get the user that executed the query just yet. So if you have that tidbit to add to this so you can filter a little easier please let us know. You can narrow the list by adding a w...
Over the last week I have done several sessions either for the Pragmatic Works Free Training online or at SQL Saturday. It has been a lot of fun, so thank you to everyone who has come out and hopefully learned something from one of the sessions I have done. So, I mentioned a couple things I was going to do, one of which is post a quick recap and sample package from the Expressions and Variables session. So here that is. The other will come next week when I post some same situations and data sets...
Thank you to everyone who attended my free webinar hosted by Pragmatic Works yesterday (September 20, 2011) on SSIS Variables and Expressions. The webinar was recorded, so if you want to reference it again or if you were unable to attend and wanted to see it you can view the recording from this link here the clicking view on the right side on the Pragmatic Works website (Here is the direct link to the view button). I will be answering questions today if I was unable to get to your question in th...
Looks like it is that time again... for me to present a FREE webinar for good ol' Pragmatic Works today at 11AM eastern standard time. The topic today will be SSIS variables and expressions. I previously did a webinar on a similar topic that also included configurations. If you were in that webinar then you are well aware that we really only got to go into detail on configurations and not the other two topics. So today is completely dedicated to variables and expressions. Feel free to bring your...
What is the difference between COUNT(*), COUNT(1) and COUNT( ColumnName )? A mystery that will never be known… ok that was a lie, but the rest of this blog is not a lie, just to be clear. :) COUNT(*) – Number of records in the table regardless of NULL values and duplicates COUNT(1) – Number of records in the table regardless of NULL values and duplicates ** IMPORTANT NOTE: The 1 does NOT refer to an ordinal location of a column. This will not count the records in the first col...
Those who know me will know that this post is a little out of the ordinary... I am posting about PowerShell. I have done one previously about Backing up and restoring site collection in SharePoint , so believe me I will admit that it has its place. Well I was onsite with a client today and found another use for PowerShell: checking the size of and free space on mount points on an HP appliance. This method will work with any machine, but this specifically happen to be on a server using mount poin...
I'll be honest, I am partially writing this because I always forget how to reset the identity column on a table and I want to be able to just pull up my blog and look it up. Yet again today I had to do this and I couldn't remember the exact syntax, so here it is. The next record is going to be the seed value plus 1, or in the case of this example I will use zero so the first record is 1. That will make my O.C.D. happy to see my table starting with a key value of 1! DBCC CHECKIDENT('<TableName...
First of all if you came out to my webinar this morning let me say a huge thank you! I really appreciate everyone taking time out of their busy days to listen to me ramble on for about an hour this morning. If you were there I hope you got some good information out of it. If you didn't get good information out of it you probably weren't paying attention... just kidding!! :) My webinar today covered Excel Services in SharePoint 2010. I covered the basics of setting up the web application and then...
Tomorrow I will be presenting a webinar at 11 AM eastern time for the Pragmatic Works Training on the T?s. This month each of the authors of the SharePoint 2010 Business Intelligence 24-Hour Trainer available from Wrox are presenting a topic they covered in the book. I happened to write the chapter on setting up and deploying Excel services applications. So tomorrow get ready for awesomeness to overtake your computer screen for an hour while I explain Excel services to y...
SQL Saturday in South Florida is quickly approaching in just 2 short weeks. As usual, if you are in the area please come out and support the wonderful people who are giving of their time to help out in the community. Don’t forget that it is a free event, so that has to be a mark in the PROS column to get you to come!! There are going to be a lot of great speakers including several from good ol’ Pragmatic Works. We have several people making the trip down for the day; I’m not sure how many will b...
Oh no you, you got the Could not load type ‘Microsoft.AnalysisServices.SharePoint.Integration.ReportGalleryView’ error!!! What to do now? Why did you get it? How to fix it? All these questions are now answered! PowerPivot is a wonderful tool with a lot of power to enable self-service BI in many organizations. There is a fine line between having users create PowerPivot reports and building an enterprise solution in something like SSAS. One of the things that blurs that line is sharing PowerPivot ...
SQL Saturday #85 is quickly approaching. It will be held in sunny Orlando, FL so if you live in the area get ready for awesome because I am coming to town! Ok, that may have been a little over the top but all parts of that thought are in fact true. I am coming to town for the event and it will be awesome. To start with if you are a SQL Server professional in any way you should come out for the day. That includes all the DBAs, .Net developers that work with SQL and of course the BI developers out...