PowerPivot Part 1: Bringing Data Together

by
No comments yet
Email

Hello everyone, Pitan here! I’ve finally had the chance to get my head around PowerPivot, the new Excel 2010 add-in for grabbing, pivoting, and displaying data. The chronicles of my journey to set up my own PowerPivot report will be revealed in a series of blog posts over the coming weeks. Tune in as I give you some HOW-TOs with a healthy dose of side commentary!

PowerPivot allows you to pull data from a wide variety of sources that you can combine into a single report. For instance, you can query a SQL Server 2008 database, extract data from an external text file, or link to tables of data within your Excel workbook, etc.

While having all the data in the world at the ready may be intoxicating for some, in this example, I’ll just stick with using data from the 2008 R2 AdventureWorks database:

1. Go to the PowerPivot tab in Excel 2010 in the ribbon

2. Click PowerPivot Window to launch PowerPivot.

3. Go to From Database and select From SQL Server

4. The connection wizard will walk you through connecting to the desired database and optional default table.

5. You will be given the option to select tables from the database or use a query. For this example, I used a pre-defined query.

Note: I found the Validate function useful. PowerPivot doesn’t allow duplicate column names, which Validate kindly pointed out to me.

6. Once the data was imported, I noticed that the default table name was Query. I changed it to be Sales Data.

There you have it: a simple data import into PowerPivot.

But what if you want to import the newest data? One does not simply refresh PowerPivot data…
Actually, it turns out that you do. PowerPivot has a one-click button to refresh data from external sources.

Tune in next time for more on PowerPivot!

Meet the Team: Sean

by
1 comment
Email
Hello and welcome to our Meet the Team series, in which we aim to give you deeper insight into the minds and personalities of those who make up this eclectic, close-knit group. We are developers, marketers, and technical support engineers, and at work we craft everything from Microsoft reporting APIs to mobile email applications. And outside of work? Let’s just say racing against the machine during hackathons, building architecturally sound beer towers during retros, and paddling down the Charles during the warmer months are simply the beginning.

Without further ado, say hello to Sean.

1. What do you do?

Write software and swear at computers.  Developers here work on pretty much the full stack, so I’ve got my fingers in everything from CSS and Javascript to Rails and CouchDB.

2. What are you listening to right now?

The Gold We’re Digging by Parts & Labor

3. Describe your personal style in one word.

Metagrobolized

4. Fill in the blank. Contrary to popular belief I  ________.
I’m not sure it counts as a popular belief, but I once had a man on the 71 bus very earnestly tell me that I should go live in Alaska.  Apparently if I showed up with a roll of quarters and the clothes on my back then the country’s largest state would take care of me.  This is, in fact, not true.  While I enjoy snow a little bit I don’t think I’d fancy a winter quite that long, and I always kind of think moose are up to something.

5. If you could build any app, what would it be and why?

Maybe this isn’t a problem everyone has and it’s just me, but I’m constantly running across links or videos or blog posts or whatever that either a) I know I’m going to need/want sometime in the future, but not right now or b) are just generically cool but not of any immediate significance.  I’ve experimented with lots of places to put these like Delicious (or its many offshoots), random notes files in Dropbox or Sticky Notes in a file somewhere, but all those have the same problem: they do an excellent job at storing the data, but are terrible at reminding me about it.  I’ll find something like a Chrome plugin that lets you have ssh sessions in your browser, which is really cool, but I don’t want to use it now (it’s in beta, I don’t have any strong need for it, etc) and so I’ll file it away for later and then when it’s later and I actually could use it, I don’t even know it exists.  If I remembered I could easily search my Delicious for it, but I don’t even remember that there’s something worth searching my Delicious for.  What I’d really like is something that I can feed links to, and it’ll periodically (maybe every morning?) pick one at random and tell me about it.  That way these cool things can stay at the periphery of my attention, and while I might not get reminded about them exactly when I need them, they won’t be lost down the memory hole forever.

6. What 3 things would you bring to a deserted island?
A record player, a copy of London Calling and a robot that I can set to press a button every hundred minutes or so.

7. When did you last laugh?
I laugh exactly once a year on April 16th when the winners of the latest round of the Lyttle Lytton contest go live.

8. What is the last thing you bought?
Excluding lunch, I recently upgraded my work peripherals with a Das Keyboard, a Microsoft Arc Touch Mouse, a Whitelines notebook and a Lamy Safari fountain pen.

9. What are your biggest pet peeves?
I made the mistake of reading something on the internet the other day, only for someone to (without a trace of irony) use the word ‘irregardless’ in a sentence.  I think I threw up a little in my mouth.  There are so many things that have to go wrong before that word makes its way across a keyboard, it boggles the mind.

10. Name something that makes you smile.
The word ‘assassafrassinate.‘  There’s a line somewhere between wordplay and incompetence that makes ‘irregardless’ terrible and ‘assassifrassinate’ wonderful, but I’m not certain where it is.

11. If you could teach a college course what would it be?
CS 350 – How to Actually Write Software.  A lot of practical software development skills –version control, testing, how to effectively collaborate with a team, planning and estimation to name a few – are largely ignored or glossed over in a traditional CS curriculum, or at least were in mine.  The most common reason for this I’ve heard is that a Computer Science degree is supposed to impart you with theoretical knowledge about computation, rather than prepare you to be a worker bee in the engines of industry.  That may be true, but it ignores the fact that even in the rarified air of academia people still need to produce useful software, and that doing that is orders of magnitude easier when you have the right tools.  I use Git on every piece of code I write, even projects that never leave my local machine, because it’s just that useful.  In my entire time as an undergraduate I never had a professor suggest that I should write an automated test suite to make sure that my code was doing what I thought it was.  In retrospect, I wouldn’t be surprised if writing effective tests wouldn’t have bumped a few of my projects up a letter grade.  If that would have been so useful to me as a student, I can’t imagine it wouldn’t be just as useful for postgraduates and researchers.  Given that these techniques are so fundamental to just about every level of writing code, it’s downright absurd and foolish that they aren’t more widely taught.

One Solution for a “Service Unavailable” Error Message in IIS6

by
No comments yet
Email

Are you encountering an “Object can’t be created” error or “Service Unavailable” error with your web applications when using a 64-bit OS and IIS6?

I was assisting a customer recently with what appeared to be a typical installation. He had a Windows Server 2003 (x64) machine, which was running IIS6. His classic ASP application was throwing an “Object can’t be created” error (ActiveX component can’t create object). This is a fairly common configuration error for customers running applications with 32-bit COM Objects on 64-bit operating systems. The typical resolution is to double check that the customer’s DLLs are registered properly in the registry. If that doesn’t fix things, we make sure that IIS6 is running in 32-bit compatibility mode:

cd %systemdrive%\Inetpub\AdminScripts
cscript.exe adsutil.vbs set W3SVC/AppPools/Enable32BitAppOnWin64 1

Unfortunately, after changing these settings his entire site was throwing a “Service Unavailable” error message.

After some research, I found that the customer’s site was using ASP.NET in addition to Classic Asp. The already registered ASP.NET 2.0 DLL was 64-bit specific and needed to be switched with a 32-bit compatible version to function with IIS6 in 32-bit compatibility mode.

The following lines of code set IIS6 to run in 32-bit compatibility mode, reinstall Asp.NET 2.0 with the correct bitness, and reset IIS to make sure that our changes take effect:

cd %systemdrive%\Inetpub\AdminScripts
cscript.exe adsutil.vbs set W3SVC/AppPools/Enable32BitAppOnWin64 1
C:\Windows\Microsoft.NET\Framework\v2.0.50727\aspnet_regiis.exe -i -enable
iisreset

With the proper Asp.NET 2.0 DLL in place, we could run IIS6 in 32-bit compatibility mode without issue, the application worked beautifully, my customer was happy, and I managed to get home in time for dinner.

Twitter Roundup: Life of a Coder

by
No comments yet
Email

As I’ve been perusing the Twittosphere, I’ve run across several entertaining tweets about the frustrations of coders everywhere bemoaning relatable #ProgrammingProblems and #CoderProblems.  A few made me chuckle out loud, and I hope they bring the same amusement to you on this Monday morning.  [ To see the story on Storify.com]

This Twitter Roundup brought to you by the frustrations of coders everywhere bemoaning relatable #ProgrammingProblems and #Coderproblems. You’re not alone!

Simple PowerShell Deployment Scripts

by
No comments yet
Email

Stsadm is the standard for MOSS 2007. Although still available in SP2010, it is deprecated and should not be used. So let’s start with some basics of PS for SP.

  1. One does not simply use PowerShell: The commands are specifically geared for the SharePoint Management Shell. If you open Management Shell as an administrator and type each command in, it’ll work fine. However, when it comes to running scripts there are some issues.
    • You have to load the Microsoft.SharePoint.PowerShell snapin, like so:  “Add-PsSnapin Microsoft.SharePoint.PowerShell”
      • This may give you an error: “The local farm is not accessible.” You need to have your account enabled as a SharePoint Admin (see below)
    • Make sure you’ve set the execution policy. This one is easy, just use the “Set-ExecutionPolicy” command. The possible values are:
      • AllSigned – You can only run trusted signed scripts.
      • RemoteSigned – If the script is downloaded, it must be signed, otherwise just run it.
      • Restricted – This one is self explanatory. No one gets to run scripts. No nobody. Not nohow.
      • Unrestricted – Again, self explanatory. As a very wise English professor once told me, “Unrestricted is the opposite of restricted.” I never forgot that…
  2. You always need root: You have to be an SP administrator in order to run SP commands from PowerShell. You can check your local permissions by:
    • Right clicking on SP Management Shell and running it as an administrator. This will give you access to admin privileges, but if your login isn’t a SP admin, then you can’t do any of this in regular PowerShell.
    • Typing “Get-SpShellAdmin”
    • Looking for your login.
    • If you aren’t on the list, you can use the current Shell to add yourself, like so: “Add-SpShellAdmin -UserName YOUR\UserName”
    • Now you should be able to execute SharePoint scripts in PowerShell.
  3. Remember – All Files .Ps1.: The script has to be saved with extension .ps1.
    • You  shoud save as “All File Types” rather than “*.txt,” otherwise it may not work.
  4. No execadmsvcjobs: That command is no longer available. So timer jobs have to be waited for a little differently. 
## Get the solution
$Solution = Get-SPSolution -identity $SolutionFileName
$lastStatus = ""
## loop whilst there is a Job attached to the Solution
while ($Solution.JobExists -eq $True)
{
 $currentStatus = $Solution.JobStatus
	 if ($currentStatus -ne $lastStatus)
	 {
		Write-Host "$currentStatus…" -foreground green -nonewline
		 $lastStatus = $currentStatus
	 }
	 Write-Host "." -foreground green -nonewline
  sleep 1
 }
 ## completed
 Write-Host ""
 Write-Host " "$Solution.LastOperationDetails -foreground green

The Script
I wrote a simple script that prompts for file name, webapp name, and what function you’d like to perform. It is based on the script found on Nik Patel’s SharePoint World blog. The difference between Nik’s and mine is that mine doesn’t check for everything before performing operations. If you choose to use it, you have to be aware that you might get an error; however, it is a very simple version and much easier to use/understand/modify.

#####
# Script to deploy Webparts/Solutions. Does not make use of feature installation
# Kate Mogel 2012
#
# To use:
# Place in folder with WSP file. Open powershell and cd into solution folder
# Run script as admin, refering to local file (e.g. ./DeployScript.ps1)
# Enter full file name (e.g. solution.wsp), web app name, and choose an operation
#####
param(
[string]$solName = "$(Read-Host 'Enter WSP File name ')",
[string]$webName = "$(Read-Host 'Enter WebAppName. i.e. http://SP2010APP ')",
[string]$whichFunct = "$(Read-Host 'Enter IS to install, RS to remove, US to upgrade ')"
)

function main{

 #Check for snapin. Add if not present
 $snapin = Get-PSSnapin | Where-Object {$_.Name -eq 'Microsoft.SharePoint.Powershell'}
 if ($snapin -eq $null)
 {
        Add-PSSnapin Microsoft.SharePoint.Powershell
 }

#Get location
$solLoc = Get-Location 

  #Check the operation
  if($whichFunct -eq "IS")
  {
 	addSol
  }
  if($whichFunct -eq "RS")
  {
      Uninstall-SPSolution -Identity $solName -WebApplication $webName
  }
  if($whichFunct -eq "US")
  {
      Update-SPSolution -Identity $solName -LiteralPath $solLoc\$solName -GACDeployment
  }

}

#Function to add and install solution
function addSol{
	$Sol = Get-SPSolution | where-object {$_.Name -eq $solName}
	if($Sol -eq $null){
	    Add-SPSolution -LiteralPath "$solLoc\$solName" -Confirm:$false
	    Install-SPSolution -Identity $solName -WebApplication $webName -GACDeployment
 	}
}
#Run Main Method
main

Okay. Go deploy your WSP.

Stories from the WIT Trenches: Jen Stirrup

by
No comments yet
Email

[This is the eighth in a series of posts exploring the personal stories of real women in technology. Every woman in tech overcame, at the very least, statistical odds to be here; this blog series aims to find out why, and what they found along the way. Author of a prestigious BI blog, Jen Stirrup (t|ln), runs a small Business Intelligence company (Copper Blue Consulting) with Allan Mitchell (t|ln) and is an active member of the SQL Server community. If reading her story inspires you to share yours, please email me.]

Meet Jen:

“I have been a SQL Server Most Valuable Professional (MVP) for nearly one year, in the SQL Server discipline. This allows me to connect more deeply with the great minds and kind hearts in the MVP community and at Microsoft. One day, I hope that I won’t be as tongue-tied when I meet the other MVPs that I’ve admired for such a long time!”

1) Can you take us back to your “eureka!” moment—a particular instance or event that got you interested in technology?

When I was eight years old, my Uncle gave us a computer that he’d fixed. It was a little Sinclair ZX81, and I loved it. I learned to program in BASIC, and my love of technology has been with me ever since.

2) Growing up, did you have any preconceived perceptions of the tech world and the kinds of people who lived in it?

My perception of the tech world was shaped by older males in my family, who took the time to involve me in all things electronic and computer-focused. For example, my grandfather was one of the first television engineers, and continued to be impressed and excited by technology until he passed away in his mid-eighties.  One of my great uncles was a spy during the Second World War, and worked to code-break Japanese codes. Their experiences combined to influence me, and continue to do so until this very day.

3) When did you first consider a career in technology? What did you envision doing?

Initially, I wanted to train as a psychologist and I had a specific interest in cognitive psychology. I used my programming skills in order to set up psychological experiments and I found that I preferred it to psychology.

I moved into Artificial Intelligence, moving from my cognitive psychology and programming background. I was fascinated by the algorithms that attempted to further research into human cognitive processes. I still see Artificial Intelligence alive and well, but in a different guise (e.g. search technologies).

4) Did you experience any personal or systemic setbacks at any point of your academic or professional career?

In the first two years of my son’s life, he was critically ill on occasion. At some points, he was given an hour to live. His illness was a constant stress, and I obviously couldn’t work as he recuperated. I’m glad to say that he survived, partially due to his own tenacity and zest for life. I’m very grateful to the doctors and nurses who saved him, despite the odds.

5) Whom do you look at as mentors and/or sources of inspiration in your field?

I am inspired every day by people in the community, particularly the Professional Association of SQL Server (SQLPASS) community. There are a huge number of selfless volunteers who give up their time to create training material, give presentations, and provide help and support to people who are on the path to learning SQL Server.

6) How has your participation in both the on- and offline SQL Server communities changed the way you look at and work with these technologies?

I’ve learned a lot about business benefits and perspectives from interacting with people in the community. Someone might ask a question which seems strange, but when you start to understand the ‘why’ of the question, it becomes clear that there may be a strong business reason for doing something, even if the proposed technical response seems strange.

I’ve met members of the Analysis Services, Excel and Reporting Services teams, and I’m hugely impressed with their dedication and innovation to provide high-quality products and solutions that SMEs can afford.

7) Why do you think the rate of attrition for women in software engineering is higher than that of women in most other tech fields?

I’m not sure if this is the case, cross-culturally. From my own experience, the issue is perceptions about returning to a technical role after maternity leave. Women leave the field for awhile, and then lose confidence to come back to technology since the tech world has moved on so fast. I have to say that, after returning to work after having had my son, women should not lose confidence in coming back to technology after having had a child. Remember most of the guys you work beside are also parents. The technical skills are transferable to newer versions and editions.

8) Do you have any suggestions for how to get more girls interested in computers and computer science? Is this important to you?

I think it is important to show girls that technology can help people. For example, Microsoft uses technology to help girls across the globe, in partnership with UNESCO.

Don’t dismiss girls from technology, at an early age. Teachers need support in the classroom to make sure that girls also get attention and equal education in subjects such as math, computing science and so on.

Twitter Roundup: Talking About SSRS

by
No comments yet
Email

Hello!  Welcome to my first post.  I’m Elise, lover of social media and self-proclaimed coffee addict.  As a newcomer to SoftArtisans, and to the MSFT tech arena in general, I’ve been trying to absorb as much info on the technologies we run on as possible. Since one of OfficeWriter’s main features is its SSRS designer, I decided to tackle this reporting beast first. Luckily, the Twittersphere is rife with helpers. Some of my favorite SSRS-related tweets (and tweeters) are below.  (Click the picture to see the full list.)  If you have any favorite SSRS bloggers, tweeters, or posts I’d love to hear about them!  Send me a tweet or leave a comment in the comments section so I can check it out.

What’s New In Office 15

by
No comments yet
Email

Photo Credit: Techmash.co.uk

Overview

Microsoft is pushing to make Office more of a service. In their ideal world everyone would be using Office 365 and there would be no on-premises environments. Since Office will be more of a service Microsoft is providing a few updates.

Better Experience Across Devices

Microsoft is putting emphasis on having your Office experience be very similar across different platforms and devices. With a push toward Office being more of a service, they realize you may be accessing Office from tablets, desktops, mobile phones or web browsers. Keeping this in mind, they have put a lot of work into improving the user experience.

  • SharePoint has better support for iPads and other mobile devices and is focused more on using standards such as HTML5 and removing non-cross-system/browser technologies such as ActiveX, Flash and Silverlight.
  • Office interfaces have been revamped with better support for touch interfaces. If you are using an office application in touch mode, then menus will appear the same, but the spacing will change to better support touch.
  • Settings can sync to the cloud to make your user experience easier. For example, if you are on page 40 of a 100 page word document and you switch from your desktop to the web, it will reopen at page 40.
  • Application Streaming – Office can be running in only a few minutes.

What is new in Word and Excel

“Agaves”

Microsoft’s attempt to unify the experience across platforms brings up an interesting area of development. What can you write that will run in a web browser, on a desktop client and a mobile phone? The answer is not VBA macros or VSTO add-ins. Microsoft has introduced a new platform of development code-named Agave. You can think of an Agave as an IFRAME which can be embedded right into your document. Microsoft exposes an API through which your Agave can communicate with Office.

For Word and Excel it looks like there will be only two types of Agaves: Task Pane Agave and In-Document Agave.

  • Word will support only the Task Pane Agave.
  • Excel will support both the Task Pane Agave and the In-Document Agave.

Examples of Outlook Agaves:

  • Displaying a map when an e-mail contains an address
  • Displaying the Yelp review when someone talks about a restaurant
  • Auto-populating a new appointment window when someone suggests a meeting time
  • Quick link to bug tracker based on bug numbers

Keep in Mind:

  • Agaves only run at a document level, and are distributed with the document.
  • In order to use Agaves in Outlook, Exchange 15 is required on the server.

SharePoint _API

The big think in SharePoint 15 from a developer’s standpoint is _API. SharePoint now exposes much of the SharePoint API through REST services, such as
http://localhost/_api/Web
http://localhost/WebURL/_api/Search
http://localhost/WebURL/_api/Taxonomy
http://localhost/WebURL/_api/BCS

SharePoint Apps

The SP15 Technical Preview Managed Object Model Software Development kit includes a bunch of changes that indicate app support (SPApp, SPAppInstance, SPAppCatalog etc).

Items to Note:

  • Ribbon is minimized by default (toggle with a pin icon)
  • Outlook has metro-style nav at the bottom
  • Outlook switched “contacts” to “people”
  • You can share documents online using Windows Live