May 3

Can I Refresh Data in an Excel Workbook from another Excel file?

I recently ran in to an issue at work that I thought I would share.  I had an Excel file that I need to use as a data source for a report that is generated from another Excel Workbook that I am using Power Pivot and Power View with to manipulate the data make some dashboards.  I made the connections and everything works fine, but the schedule to refresh doesn’t work. I could do a manual refresh, but it appears you cannot schedule a refresh from another workbook.

I found the below at:  schedule-data-refresh-for-workbooks-in-power-bi-for-office-365 and using another Excel file isn’t listed as a data source that is can automatically refresh from.

Report data in an Excel workbook can come from many external data sources. You can schedule automatic data refreshes for these external sources:

Power BI Cloud Service

  • Microsoft Azure SQL Database
  • SQL Server in Microsoft Azure Virtual Machines
  •  OData
  • Basic authentication
  • Anonymous authentication
  • SP Lists
  • ProjectOnline feeds

On-premises

  • SQL Server 2005 and above
  •  Oracle 10g, 11g and 11gR2

A workbook accesses external data through an embedded connection string or URL you specify when you import the original data into the workbook. Data refresh reuses the original connection information stored in the workbook.

A workbook can have only one data refresh schedule. Typically, if you create the workbook you define the schedule.

This seems like an obvious miss for Microsoft and something I hope they rectify soon.

Stay tuned for more details…

Leave a Comment

April 12

What is Power BI?

Microsoft recently rolled out a really great product in the field of self service business intelligence, PowerBi for Office365.  In February and March I went through Microsoft’s Partner training for Planning& Designing SharePoint Business Critical Solutions – Power BI.  It was really cool to be one of the first people to go through the training and see what Power BI was and all it has to offer potential clients.  This is the first in a series of planned posts to take a deep dive into what Power BI has to offer and demonstrate its capabilities and value.

Below is a short description of what Power BI for Office 365 is:

Power BI for Office 365, a self-service business intelligence (BI) solution delivered through Excel and Office 365 which provides information workers with data analysis and visualization capabilities to identify deeper business insights either on-premise or within a trusted cloud environment. With Power BI for Office 365, customers can connect to data in the cloud or extend their existing on premise data sources and systems to quickly build and deploy self-service BI solutions hosted in Microsoft’s enterprise cloud.

Power BI for Office 365 enables customers to do more with their data:

  • Discover and connect to data sources spread within and beyond the geographical boundaries of an organization.
  • Analyze and present insights from data in compelling visual formats either on premises or in the cloud from Excel.
  • Share reports and data sets online with data that is always kept up to date.
  • Ask questions of your data using natural language search and get immediate answers through interactive tables, charts and graphs.
  • Access and stay connected to data and reports from your mobile devices wherever you are.

Power BI works hand-in-hand with Excel 2013 and Excel Services, specifically taking advantage of Power Query, Power Pivot, Power View and Power Map:

Power BI

Power Query for Excel enhances self-service business intelligence (BI) for Excel with an intuitive and consistent experience for discovering, combining, and refining data across a wide variety of sources including relational, structured and semi-structured, OData, Web, Hadoop, Azure Marketplace, and more. Power Query also provides you with the ability to search for public data from sources such as Wikipedia

Power Pivot is an Excel 2013 add-in you can use to perform powerful data analysis and create sophisticated data models. With Power Pivot, you can mash up large volumes of data from various sources, perform information analysis rapidly, and share insights easily.

Power View is an interactive data exploration, visualization, and presentation experience that encourages intuitive ad-hoc reporting. Power View is a feature of Microsoft Excel 2013, and of Microsoft SharePoint Server 2010 and 2013 as part of the SQL Server 2012 Service Pack 1 Reporting Services Add-in for Microsoft SharePoint Server Enterprise Edition.

Power Map for Excel 2013 is a three-dimensional (3-D) data visualization tool that lets you look at information in new ways. You can discover insights that you might not see in traditional two-dimensional (2-D) tables and charts.  With Power Map, you can plot geographic and temporal data on a 3-D globe, show it over time, and create visual tours you can share with other people.

Using these great new tools for accessing and manipulating data, you can create some awesome reports and dashboards in Excel and share them with your Power BI for Office 365 site.  One of the great feature in PowerBI is Q&A.  Q&A is a tool that allows you to drill down on data with simple English Queries.  For Example if you have a report on GDP by country by year, your user can just type: “Show me USA and UK GDP for 2008 through 2013″ and the report will filter as you type.   Your end-users will be able to do their own analysis and be able to gain insights without always having to involve SQL resources.

While Power BI is a great step forward toward Self-Service BI, there is still a lot of work that needs to be done by the IT professionals with regard to setting up and governing the resources, administering the Power BI sites, and training.  I look forward to seeing how Power BI grows as a product offering and how businesses are able to utilize it to gain new insights in to their data and processes.

Leave a Comment

February 13

What’s Craig doing now?

I finally finished up my MVC 4 project.  It was good learning experience with some new technologies, but the biggest lessons learned revolved around project and client management.  Sadly they weren’t new lessons, but things that I just kind of forgot about or let slide.

  1. Business requirements are good, but you need specific and detailed functional requirements.
  2. If the client doesn’t respond to your communications, keep after them even though you feel like you hounding them.  Keep a log of contact attempts.
  3. Get everything in writing and document everything you can.
  4. If scope or functionality changes, get it writing.
  5. Change controls are your friends! Don’t be afraid of them. They protect everyone’s interests.

I just started another engagement which is modernization project for a client.  I am involved with the SharePoint 2013 implementation and app development pieces.  I will be posting a lot of lessoned learned around that.  Currently, I have been working on some on premise provider hosted apps, which I am not finding the easiest things to set up.  More information to follow on that.

I have also been doing a deep dive on PowerBI for Office365 with Excel services.  It is really a cool product and should have a huge impact for business users.  I will be presenting Intro to PowerBi at my user group in March and at 2 SharePoint Saturdays – Twin Cities and Chicago Suburbs – on April 5th and May 17th respectively.

Lastly, I will be helping out my company doing tech interviews.  I will be posting general interview questions here on a regular basis to help me with a bank of questions and to help me keep my skills sharp.

August 28

.NET MVC 4 Web Application

I have been assigned a new project at work. I am the system architect and team lead working to develop a new web application for our client. This is a green field development that is being built using .NET MVC 4 with heavy use of HTML 5 and JavaScript for the user interface and MSSQL Server 2012 for the backend. The end solution will connect to QlikView to generate dashboards and spot trends in the data.

This is a great opportunity, but it will take me out of the SharePoint realm for a little while. The upside is that I am doing more architecture work, I am be working with the latest technologies, and I am be refreshing my .NET MVC skills.

Category: .NET, Development, MVC 4
May 14

.PDF Files Prompting to Save Before Opening

I recently migrated a site from 2007 to 2010.  Yes, I know 2010 is so last year and everyone is going to 2013, but my company is a little slow.  After the upgrade, I had users mentioning that .pdf files were no longer just opening when clicked on, but were prompting to save first.

A quick search showed that this was due to restrictive permissions.  The best solution is to add the MIME type for .pdf to allow the file to open without prompting to save first.

$webApp = Get-SPWebApplication ("http://yourSeverName.com/")
$webApp.AllowedInlineDownloadedMimeTypes.Add("application/pdf")
$webApp.Update()

I found the solution here at Trevor Seward’s blog: http://sharepoint.nauplius.net/2011/06/browser-file-handling-and-avoiding-permissive-mode/  I am posting the information here so that it’s in a place that is easier for me to find.

April 5

Elgin SharePoint Users Group Meeting

Yesterday we had another meeting of the Elgin SharePoint Users group I head up. http://elginsharepoint.org
We had a decent turnout, about 10 people. Our scheduled speaker could not make it so we just had an open forum. We discussed deciding on how many servers you needed for a SharePoint farm, some branding, some PowerShell, InfoPath and some administration aspects of SharePoint. All pretty good discussions.

The rest of the evening we discussed the up coming SharPoint Saturday scheduled for June 1st in Addison, Il. http://www.spschicagosuburbs.com for completed details. Since our group is co-hosting the event we went through the some of the proposed speaker submissions and it looks like we will have a broad range of topics to discuss. I am really looking forward to what should be a great event.

March 30

SharePoint Saturday in Addison, IL – June 1

I am working with a group of people to host a SharePoint Saturday event in Addison, IL on June 1st.

It is being co-hosted by the Chicago Share-A-Pint group of Lombard and the Elgin SharePoint Users Group.

Join SharePoint architects, developers and information workers for our next ‘SharePoint Saturday’ event. SharePoint Saturday is an educational, informative and lively day filled with sessions from respected SharePoint professionals and MVPs. We cover a wide variety of SharePoint topics with sessions for Information Workers, IT Pros and Developers. We also offer a SharePoint 101 track and hands-on labs! SharePoint Saturday is FREE, open to the public and is your local chance to immerse yourself in SharePoint!

For more information see http://www.spschicagosuburbs.com/

For more informaiton on SharePoint Saturday events in general see SPS Events.

Thanks,

Craig

January 27

Get User ID from People Picker Name Field

I have a form library that is surfaced to a page and it uses the User ID of an employee\manager pairing and compares it to the Current User ID for security. If the Current User ID doesn’t mathc either the Employee or Manager User ID, then you get a Not Authorized to view form.

For some reason, when I created the forms and populated the fields, the User ID fields didn’t populate correctly. Only the People Picker fields of the Employee and Manager Name fields populated the way they should. As a result anyone who didn’t populate correctly got the Not Authorized view. I needed to come up with a way to update the User ID fields by looking the Name fields, retrieving the UserID, and updating the appropriate fields.

I couldn’t find a script that did what I needed to do, but I did find similar scripts to what I need.   I was able to piece together what I found to come up with the script below and it did exactly what I needed.  The main piece revolves around calling New-Object Micros@ft.SharePoint.SPFieldUserValue($web,$employee) and is a pretty straight forward.

#Site and List
$web = Get-SPWeb  "http://yoursite.com/locationOfList/"
$listToUpdate = "Your ListName";

#Get List
$list = $web.Lists[$listToUpdate]

#set Counter
$count=0

# Iterate through all Items in List. 
# Micros@ft is spelled incorrectly below in the code, 
#   because the site was creating link to Microsoft's web site
#   and not rendering appropriately

    foreach ($item in $list.Items) {

            #Get Employee User ID from People Picker Employee Name field
            $employee = $item["Employee Name"]
            $employeeObj = New-Object Micros@ft.SharePoint.SPFieldUserValue($web,$employee)
            $employeeUserID = $employeeObj.User

            #Get Manager User ID from People Picker Manager Name field
            $manager = $item["Manager Name"]
            $managerObj = New-Object Micros2ft.SharePoint.SPFieldUserValue($web,$manager)
            $managerUserID = $managerObj.User

            #update item fields
            $item["Employee User Id"] = $employeeUserID
            $item["Manager User Id"] = $managerUserID
            $item.Update();

            $count = $count+1
            Write-Host "item" $count "updated:  " $item.name " Employee ID: " $employeeUserID " Manager ID: " $managerUserID
    }

    Write-Host "Total records update: " $count

$web.Dispose();

If you have a similar issue, I hope this helps.

January 21

Copying and Updating a SharePoint Designer 2007 Workflow

I recently had to update a process for performance agreements my company has on a SharePoint 2007 environment.  We were just copying the process from 2012 and doing it again for 2013.  I created document libraries with the same name as the old ones and added a 2013 to it.

The workflows is almost the same, except it needed to point to the new document library. Here are the steps I used to create my copy.

1. Right click on the workflow I wanted to copy and select copy.

clip_image001

2.  Paste the copy in the same folder, then right click on it and select Rename.

clip_image002

3. Put the name you want to call it.  I just used the same name and added a 2013 to it.

clip_image003

4.  Check Out the Workflow

clip_image004

5.  If you double click on the folder, you see the 4 files in the folder.

clip_image005

6. Right click on them and rename them.

clip_image006

7.  The first file I edited was the .wfconfig file.

clip_image012

8.  You need to update the ListID GUID  and the names of the references.

clip_image014

9.  The next file I updated was the .aspx file.

clip_image008

10.  You need to update the ListID Guid and you may want to update the name that is displayed on the page where you start the workflow.

clip_image010

11.  The last file you need to update is the actual workflow .xoml file.  You will see all references are to the old List.

clip_image019

12.  If you click on them to edit, you will get a popup to Define the Workflow Lookup.  You don’t have to change anything, just click OK.

clip_image020

13.  You will see the name update automatically.

clip_image021

14.  After you save that file, you just have to Check In the workflow.

clip_image022

15.  Then just make sure that the workflow is correctly associated to your list.

clip_image016

If everything was done correctly you should see the correct workflow.

clip_image018

You maybe working with SP2010 by now, but the process is still similar and I know there still several companies out there using SP2007.  I hope you find this helpful.

January 20

POSH Tip – Writing Errors to a File Using Add-Content

This is a quick PowerShell tip to make error reporting easier.  In your catch block you can use Add-Content to write to a text file. Scenario: I was running a script to load data for a couple of thousand employees into a SharePoint list.

I originally had just a write-host command to let me know when a person’s information rejected. Of course I soon realized that I would have to tell someone who rejected. A quick search through Get-Command let me to the Add-Content Cmdlet

  Catch [system.exception]
  {
     $count = $count+1
     Write-Host "Error processing $EmployeeUserId $ManagerUserId" -ForeGroundColor Red
     Add-Content C:\SharePoint\POSHscripts\2013LoadEmployees\rejects.txt "Error processing $EmployeeUserId $ManagerUserId "
  }

}
   #Add this outside of your Try Loop to give you a total at the end of your file.
   Add-Content C:\SharePoint\POSHscripts\2013LoadEmployees\rejects.txt "Total Rejected: $count"

Worked like a charm and I had a quick an easy report of my 36 rejects.