This topic contains 12 replies, has 3 voices, and was last updated by 
-
Posts
-
I have been working with PowerPivot for the past 2-3 months as my schedule permitted. During this time I have created a very powerful data model (currently it’s around 3GB) that I would like to share with others within the company. In addition, I want to start introducing Power Pivot to others.
Is there a document(s) that outline the requirements (hardware and software) and the processes to stand-up SharePoint to support this? I would like to have the data models refreshed at a scheduled time nightly. For instance, can this be done using on premise SharePoint, or are you required to us the SharePoint Online for BI?
Hello John,
Power Pivot workbooks can be shared with on-premise SharePoint, but there are some requirements that must be met (including licenses for Enterprise Client Access and licenses for SQL Server Business Intelligence). Some resources for you to look into:
Building Data Models with PowerPivot by Alberto Ferrari and Marco Russo, Chapter 5, Publishing to SharePoint (pp. 117-132). See also Chapter 16, Comparing Excel and SQL Server Analysis Services (pp. 463 – 472) which also includes comparisons with SharePoint.
Dashboarding and Reporting with Power Pivot and Excel by Kasper de Jonge, Chapter 6, Sharing Dashboards and Reports within an Organization (pp. 141 – 174)
The “Great PowerPivotPro FAQ” ( http://powerpivotfaq.com/Lists/TGPPF/AllItems.aspx ) also has a couple topics, namely Topic I (Publishing on SharePoint) and Topic J (Report Gallery).
Thanks – I have the books you mentioned in my library so I’ll review the pages you noted.
I’m curious, the “Great PowerPivotPro FAQ” article comments about SharePoint 2010 and it has a limit file size of 2048MB. do you know if this has increased with SharePoint 2013? The data model I created is 2851MB.
In Software boundaries and limits for SharePoint 2013 it reads:
The default maximum file size is 250 MB. This is a configurable limit that can be increased up to 2 GB (2,047 MB). However, a large volume of very large files can affect farm performance.
—-
Can the one workbook be divided into several?Based on some of the information that I have reviewed, it appears that data models stored on SharePoint can be accessed as a data source… that sounds very encouraging. If that is the case, can one spreadsheet access more than one data model? If so, then yes, I could split the one large data model into 2 or 3 smaller models – which would be my preference.
Something else to consider: if your Power Pivot model gets its data from “linked tables” in Excel worksheets within the same workbook, your workbook size could be cut in half by connecting to external data sources (Power Pivot makes copies of linked tables, even though they are in the same workbook as the data model).
Regarding whether a spreadsheet could access more than one Power Pivot model, I am inclined to say no–but in this case would be glad to hear that I am wrong.
100% of the data is being extracted directly from our SQL database into the PowerPivot data model.
John,
Sometimes it is possible to “pre-aggregate” data as it leaves SQL Server. For example, let’s say the raw data is imported at the lowest possible transaction level (perhaps many times per day or hour), but is never analyzed below the month level; to save on file size, the data could be aggregated by month during import.
Tom – when I used “standard” Excel, I was forced to use the “pre-aggregate” data – it was the only way to get the data set below the 1million row limitation. It’s wonderful how you can extract 10’s of million on rows and having several “tables” joined together in the data model with Power Pivot.
Hey Powerpivotpro,
I have a slightly different challenge that more or less falls into this category.
We are currently trying to use the automated scheduled refresh from Sharepoint with power pivot for Sharepoint 2013. Everything works. However, the refresh does not work if we add VBA buttons in the workbook. Indeed, we have a VBA code that is very useful for sharing: a button that transforms a selection or the whole workbook automatically into a pdf, opens outlook and attaches the pdf to the email. Then, the analyst/manager just needs to enter the email address(es) of his colleagues and they get their view of the dashboard on a pdf (good alternative to SSAS tabular and RLS or creating many thin reports, although not interactive).
So, is there a way to auto refresh a workbook that has VBA codes inside? Is it normal that it doesnt work in sharepoint 2013? We do know it is normal that the buttons do not appear in the browser based dashboard though…
Would Power Update be the only way? Would it even be a possible solution?
Thanks a lot for your help,
Cheers
Kevin
Hi,
Kevin please start a new topic and use “Power Update” as part of the title.
Kevin,
I do not know much about SharePoint, but I believe VBA code is not executable in a SharePoint environment. Hopefully another forum participant can provide a better answer.
Tom
Hi Tom,
Thanks for your quick reply. I posted the question under power update.
Cheers,
Kevin
The forum ‘Power Pivot’ is closed to new topics and replies.