{"id":1477,"date":"2015-03-29T05:52:00","date_gmt":"2015-03-29T05:52:00","guid":{"rendered":"https:\/\/mmman.itgroove.net\/?p=1477"},"modified":"2023-02-24T21:39:46","modified_gmt":"2023-02-24T21:39:46","slug":"how-powerpivot-works-with-sharepoint-and-how-it-doesnt","status":"publish","type":"post","link":"https:\/\/regroove.ca\/archive\/2015\/03\/29\/how-powerpivot-works-with-sharepoint-and-how-it-doesnt\/","title":{"rendered":"How PowerPivot Works with SharePoint (and How it Doesn\u2019t)"},"content":{"rendered":"

A while back a customer wanted to combine external data sources and SharePoint together, and they thought the best way to do it was with Excel PowerPivot. Their intentions were in the right vein, but the possible path of execution didn’t actually exist. The idea being that they wanted to use a tool (Excel PowerPivot in their minds) to bring together a bunch of data sources and then store that data in a SharePoint list. As I described for them (below), this isn’t actually possible (using Excel), but it is possible using BDC. Here’s a snippet of my conversation with them in this regard.<\/p>\n

\n

I believe what you’re trying to accomplish is drawn out below.<\/p>\n

\"\"<\/p>\n

It sounds like what you want is to take data out of a number of different data sources, and using Excel PowerPivot, put this data into SharePoint (the raw data) as list data in a SharePoint list.<\/p>\n

Unfortunately what I just described is a marvellous, mythical beast that doesn’t exist. You can<\/em><\/strong><\/span> do a portion of what you described, but the link between exporting the data from Excel to SharePoint is a technical limitation which does not exist.<\/p>\n

What does exist today is the following.<\/p>\n

\"\"<\/p>\n

Within SharePoint, the only thing you can do with the data from Excel is surface that data – such as on a dashboard \/ web part page – in a web page in SharePoint. Here’s the key point **<\/strong> SharePoint itself isn’t aware of the data, just that it’s reserved screen space for Excel services to put some kind of “Excel thing” in that space. What that is, SharePoint has no further knowledge of ** <\/strong>(The same is true of Office 365 and PowerBI \u2013 there’s no way that I know of to get that data out of Excel and into a SharePoint list.)
\n<\/strong><\/p>\n

What you’re describing is the concept of BDC, which in the top image replaces Excel Services\/PowerPivot.<\/p>\n

\"\"<\/p>\n

Unfortunately, as highlighted in this blog post<\/a>, BDC leaves many things to be desired, as data sources other than MS SQL can be a great challenge. At this point, this is the only supported solution (that I am aware of) to accomplish what you’re describing.<\/p>\n

 <\/p>\n","protected":false},"excerpt":{"rendered":"

A while back a customer wanted to combine external data sources and SharePoint together, and they thought the best way to do it was with Excel PowerPivot. Their intentions were in the right vein, but the possible path of execution didn’t actually exist. The idea being that they wanted to use a tool (Excel PowerPivot … <\/a><\/p>\n","protected":false},"author":13,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"inline_featured_image":false,"footnotes":""},"categories":[17,21,30,7,32],"tags":[],"acf":[],"yoast_head":"\nHow PowerPivot Works with SharePoint (and How it Doesn\u2019t) - Archive<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/regroove.ca\/archive\/2015\/03\/29\/how-powerpivot-works-with-sharepoint-and-how-it-doesnt\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How PowerPivot Works with SharePoint (and How it Doesn\u2019t) - Archive\" \/>\n<meta property=\"og:description\" content=\"A while back a customer wanted to combine external data sources and SharePoint together, and they thought the best way to do it was with Excel PowerPivot. Their intentions were in the right vein, but the possible path of execution didn’t actually exist. The idea being that they wanted to use a tool (Excel PowerPivot …\" \/>\n<meta property=\"og:url\" content=\"https:\/\/regroove.ca\/archive\/2015\/03\/29\/how-powerpivot-works-with-sharepoint-and-how-it-doesnt\/\" \/>\n<meta property=\"og:site_name\" content=\"Archive\" \/>\n<meta property=\"article:published_time\" content=\"2015-03-29T05:52:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-02-24T21:39:46+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/regroove.ca\/archive\/wp-content\/uploads\/sites\/6\/2015\/04\/042915_0551_HowPowerPiv1.jpg\" \/>\n<meta name=\"author\" content=\"Colin Phillips (Alumni)\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Colin Phillips (Alumni)\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/regroove.ca\/archive\/2015\/03\/29\/how-powerpivot-works-with-sharepoint-and-how-it-doesnt\/\",\"url\":\"https:\/\/regroove.ca\/archive\/2015\/03\/29\/how-powerpivot-works-with-sharepoint-and-how-it-doesnt\/\",\"name\":\"How PowerPivot Works with SharePoint (and How it Doesn\u2019t) - Archive\",\"isPartOf\":{\"@id\":\"https:\/\/regroove.ca\/archive\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/regroove.ca\/archive\/2015\/03\/29\/how-powerpivot-works-with-sharepoint-and-how-it-doesnt\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/regroove.ca\/archive\/2015\/03\/29\/how-powerpivot-works-with-sharepoint-and-how-it-doesnt\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/regroove.ca\/archive\/wp-content\/uploads\/sites\/6\/2015\/04\/042915_0551_HowPowerPiv1.jpg\",\"datePublished\":\"2015-03-29T05:52:00+00:00\",\"dateModified\":\"2023-02-24T21:39:46+00:00\",\"author\":{\"@id\":\"https:\/\/regroove.ca\/archive\/#\/schema\/person\/adeb0df1cc7a862160be620ca7eace1b\"},\"breadcrumb\":{\"@id\":\"https:\/\/regroove.ca\/archive\/2015\/03\/29\/how-powerpivot-works-with-sharepoint-and-how-it-doesnt\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/regroove.ca\/archive\/2015\/03\/29\/how-powerpivot-works-with-sharepoint-and-how-it-doesnt\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/regroove.ca\/archive\/2015\/03\/29\/how-powerpivot-works-with-sharepoint-and-how-it-doesnt\/#primaryimage\",\"url\":\"https:\/\/regroove.ca\/archive\/wp-content\/uploads\/sites\/6\/2015\/04\/042915_0551_HowPowerPiv1.jpg\",\"contentUrl\":\"https:\/\/regroove.ca\/archive\/wp-content\/uploads\/sites\/6\/2015\/04\/042915_0551_HowPowerPiv1.jpg\",\"width\":580,\"height\":428},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/regroove.ca\/archive\/2015\/03\/29\/how-powerpivot-works-with-sharepoint-and-how-it-doesnt\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Blog Archive\",\"item\":\"https:\/\/regroove.ca\/archive\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How PowerPivot Works with SharePoint (and How it Doesn\u2019t)\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/regroove.ca\/archive\/#website\",\"url\":\"https:\/\/regroove.ca\/archive\/\",\"name\":\"Archive\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/regroove.ca\/archive\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/regroove.ca\/archive\/#\/schema\/person\/adeb0df1cc7a862160be620ca7eace1b\",\"name\":\"Colin Phillips (Alumni)\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/regroove.ca\/archive\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/14eeab0d377e9630e0983d9c08911979?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/14eeab0d377e9630e0983d9c08911979?s=96&d=mm&r=g\",\"caption\":\"Colin Phillips (Alumni)\"},\"url\":\"https:\/\/regroove.ca\/archive\/author\/cphillips\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How PowerPivot Works with SharePoint (and How it Doesn\u2019t) - Archive","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/regroove.ca\/archive\/2015\/03\/29\/how-powerpivot-works-with-sharepoint-and-how-it-doesnt\/","og_locale":"en_US","og_type":"article","og_title":"How PowerPivot Works with SharePoint (and How it Doesn\u2019t) - Archive","og_description":"A while back a customer wanted to combine external data sources and SharePoint together, and they thought the best way to do it was with Excel PowerPivot. Their intentions were in the right vein, but the possible path of execution didn’t actually exist. The idea being that they wanted to use a tool (Excel PowerPivot …","og_url":"https:\/\/regroove.ca\/archive\/2015\/03\/29\/how-powerpivot-works-with-sharepoint-and-how-it-doesnt\/","og_site_name":"Archive","article_published_time":"2015-03-29T05:52:00+00:00","article_modified_time":"2023-02-24T21:39:46+00:00","og_image":[{"url":"https:\/\/regroove.ca\/archive\/wp-content\/uploads\/sites\/6\/2015\/04\/042915_0551_HowPowerPiv1.jpg"}],"author":"Colin Phillips (Alumni)","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Colin Phillips (Alumni)","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/regroove.ca\/archive\/2015\/03\/29\/how-powerpivot-works-with-sharepoint-and-how-it-doesnt\/","url":"https:\/\/regroove.ca\/archive\/2015\/03\/29\/how-powerpivot-works-with-sharepoint-and-how-it-doesnt\/","name":"How PowerPivot Works with SharePoint (and How it Doesn\u2019t) - Archive","isPartOf":{"@id":"https:\/\/regroove.ca\/archive\/#website"},"primaryImageOfPage":{"@id":"https:\/\/regroove.ca\/archive\/2015\/03\/29\/how-powerpivot-works-with-sharepoint-and-how-it-doesnt\/#primaryimage"},"image":{"@id":"https:\/\/regroove.ca\/archive\/2015\/03\/29\/how-powerpivot-works-with-sharepoint-and-how-it-doesnt\/#primaryimage"},"thumbnailUrl":"https:\/\/regroove.ca\/archive\/wp-content\/uploads\/sites\/6\/2015\/04\/042915_0551_HowPowerPiv1.jpg","datePublished":"2015-03-29T05:52:00+00:00","dateModified":"2023-02-24T21:39:46+00:00","author":{"@id":"https:\/\/regroove.ca\/archive\/#\/schema\/person\/adeb0df1cc7a862160be620ca7eace1b"},"breadcrumb":{"@id":"https:\/\/regroove.ca\/archive\/2015\/03\/29\/how-powerpivot-works-with-sharepoint-and-how-it-doesnt\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/regroove.ca\/archive\/2015\/03\/29\/how-powerpivot-works-with-sharepoint-and-how-it-doesnt\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/regroove.ca\/archive\/2015\/03\/29\/how-powerpivot-works-with-sharepoint-and-how-it-doesnt\/#primaryimage","url":"https:\/\/regroove.ca\/archive\/wp-content\/uploads\/sites\/6\/2015\/04\/042915_0551_HowPowerPiv1.jpg","contentUrl":"https:\/\/regroove.ca\/archive\/wp-content\/uploads\/sites\/6\/2015\/04\/042915_0551_HowPowerPiv1.jpg","width":580,"height":428},{"@type":"BreadcrumbList","@id":"https:\/\/regroove.ca\/archive\/2015\/03\/29\/how-powerpivot-works-with-sharepoint-and-how-it-doesnt\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Blog Archive","item":"https:\/\/regroove.ca\/archive\/"},{"@type":"ListItem","position":2,"name":"How PowerPivot Works with SharePoint (and How it Doesn\u2019t)"}]},{"@type":"WebSite","@id":"https:\/\/regroove.ca\/archive\/#website","url":"https:\/\/regroove.ca\/archive\/","name":"Archive","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/regroove.ca\/archive\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/regroove.ca\/archive\/#\/schema\/person\/adeb0df1cc7a862160be620ca7eace1b","name":"Colin Phillips (Alumni)","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/regroove.ca\/archive\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/14eeab0d377e9630e0983d9c08911979?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/14eeab0d377e9630e0983d9c08911979?s=96&d=mm&r=g","caption":"Colin Phillips (Alumni)"},"url":"https:\/\/regroove.ca\/archive\/author\/cphillips\/"}]}},"_links":{"self":[{"href":"https:\/\/regroove.ca\/archive\/wp-json\/wp\/v2\/posts\/1477"}],"collection":[{"href":"https:\/\/regroove.ca\/archive\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/regroove.ca\/archive\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/regroove.ca\/archive\/wp-json\/wp\/v2\/users\/13"}],"replies":[{"embeddable":true,"href":"https:\/\/regroove.ca\/archive\/wp-json\/wp\/v2\/comments?post=1477"}],"version-history":[{"count":1,"href":"https:\/\/regroove.ca\/archive\/wp-json\/wp\/v2\/posts\/1477\/revisions"}],"predecessor-version":[{"id":1851,"href":"https:\/\/regroove.ca\/archive\/wp-json\/wp\/v2\/posts\/1477\/revisions\/1851"}],"wp:attachment":[{"href":"https:\/\/regroove.ca\/archive\/wp-json\/wp\/v2\/media?parent=1477"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/regroove.ca\/archive\/wp-json\/wp\/v2\/categories?post=1477"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/regroove.ca\/archive\/wp-json\/wp\/v2\/tags?post=1477"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}