August 30, 2010 by dennyglee
A long time ago, but not in a galaxy too far away…actually, just in Redmond, WA,… there was A NEW HOPE for easier PowerPivot for SharePoint installations! 
So hats off to the Analysis Services product team for building an easier PowerPivot for SharePoint installation by providing us with the “New Server” installation choice. Yet, even with the easier installation steps, it is important to make sure you do the steps in the right order and have the correct prerequisites installed to make it a smooth installation experience.
During the CTP (Community Technology Preview) process, a number of us had got together to create the PowerPivot for SharePoint installation guides. We had created a set of draft copies that were available at http://powerpivotgeek.com/server-installation/single-server-install/. Ultimately, a great installation guide is also found in the How to: Install PowerPivot for SharePoint on a New SharePoint Server.
But there is always the ask to create a guide with pictures and/or video – which I have to admit makes things a lot easier to read and install at the same time. Because of this, there are two new very useful tools to help out:
1) PowerPivot for SharePoint: Single Server Installation Guide – This guide contains the installation steps plus plenty of screenshots to help make the install steps a little easier.
2) PowerPivot for SharePoint: Single Server Installation Video – This video is created by the illustrious PowerPivotGeek – i.e. Dave Wickert. Informing and enjoyable at the same time!
Hope this makes your PowerPivot for SharePoint Single Server Installation more enjoyable!….or at least less painful, eh?!
Enjoy…or not 
Posted in PowerPivot | Tagged SharePoint, Installation | Leave a Comment »
August 24, 2010 by dennyglee
You’re sh*&ting me. No way. Man, that sucks. rocks!
- Toby Lee Shavers, from XXX (2002), altered of course!
And before you start, yes, I’m starting to follow the schtick of quotes from fellow PowerPivot bloggers Rob Collie (@PowerPivotPro) and Kasper de Jonge (@Kjonge). For more memorable movie quotes, check out http://www.powerpivotpro.com/
.
After about 14 months of testing, validation, analysis, and all sorts of hardware headaches and dancing, I am proud to say that I have finally published the Analysis Services ROLAP for SQL Server Data Warehouses whitepaper with my trusted partners-in-crime Thomas Kejser and Kay Unkroth. Thomas for his technical prowess and Kay in his ability to translate a lot of complex concepts (and frankly, my floating in outer space) into something actually cohesive.
So what is the ROLAP paper really about?
What this paper is really about is that:
- Yes, ROLAP is a viable option for high performance SSAS
- But, it isn’t easy, there are disadvantages, and … did I mention that it isn’t necessarily easy?
But, if you want to work with high performance ROLAP – here is a technical case study on how to do it as well as the advantages and pitfalls of doing this.
As well, here’s the official summary:
Summary: This technical case study describes how the SQL Server® Customer Advisory Team (SQLCAT), in collaboration with SQL Server developers, tested and optimized a large Online Analytical Processing (OLAP) solution based on SQL Server 2008 Analysis Services by using the Relational OLAP (ROLAP) storage mode. The study examines ROLAP system requirements and usage scenarios, highlights advantages and disadvantages of ROLAP in comparison with Multidimensional OLAP (MOLAP), and evaluates various ROLAP-related data warehouse (DW) optimization techniques regarding their effectiveness and limitations.
This case study is for data warehouse architects, database administrators, and storage engineers, and assumes the audience is already familiar with the concepts of large-scale data warehouse designs for servers, storage subsystems, and databases. A high-level understanding of Analysis Services optimization techniques for cube processing and query performance is also helpful. Detailed information is available in the SQL Server 2008 Analysis Services Performance Guide at http://go.microsoft.com/fwlink/?LinkId=165486.
To continue reading, click through to the whitepaper: Analysis Services ROLAP for SQL Server Data Warehouses
.
Enjoy!
Posted in Analysis Services | Tagged Performance, Internals, ROLAP, Data Warehouse | Leave a Comment »
August 21, 2010 by dennyglee
One of the cool things with PowerPivot is to click on an SharePoint List’s Export as Data Feed (i.e. an ATOM feed) and its data can be imported directly into a PowerPivot for Excel workbook. To get this thing to work, one of the key things that I remind people regularly is to make sure you install ADO.NET Data Services Update for .NET Framework 3.5 SP1.
So after doing this multiple times, I proceeded to do my usual atom feed import like I usually do:
Repro the issue
1) Go to my SharePoint List and click on Export as Data Feed
2) When requested to do a File Download of the List.atomsvc file, click Open
3) Because you installed PowerPivot on your box, it will give you the option to import this ATOM feed into an existing (e.g. Book1) or new workbook. Choose appropriately and click OK.
4) It’ll provide you the Table Import Wizard so you can import your feed
5) Everything works like usual in terms of a PowerPivot data import
![clip_image001[5]](http://dennyglee.files.wordpress.com/2010/08/clip_image0015_thumb.jpg?w=431&h=204 "clip_image001[5]")
.
What did I expect?
6) But instead of the nice message indicating that you had imported data properly:
so it shows up nicely in your PowerPivot workbook
.
What did I actually get?
7) you get this strange error message:
![clip_image001[7]](http://dennyglee.files.wordpress.com/2010/08/clip_image0017_thumb.jpg?w=449&h=120 "clip_image001[7]")
in text this is:
For security reasons DTD is prohibited in this XML document. To enable DTD processing set the ProhibitDtd property on XmlReaderSettings to false and pass the settings into XmlReader.Create method.
So how do I FIX this?
Thanks to John McConnell for reminding me that sometimes the solution is to do something simple (for context, I just had published the PowerPivot Security Architecture so my brain is still fried).
So the solution is to simply re-install ADO.NET Data Services Update for .NET Framework 3.5 SP1 again. Yup, rather long bit of writing to simply say – install it again!
But there you have it 
Posted in PowerPivot | Tagged SharePoint, Troubleshooting | Leave a Comment »
July 26, 2010 by dennyglee
If you’re going to TechReady 11 at the Washington State Convention Center this week in warm, sunny Seattle – first of all, welcome to the Emerald City!
And if you’re a coffee snob like me (to give you background, not only am a long time Seattle resident so I pretty much breathe coffee – but I also used to live in Montréal so we’re real coffee snobs there, eh?!) – the best place for coffee without a doubt is Espresso Vivace! The closest one is the one across from REI but there is also two on Broadway in Capital Hill. So in between meetings, sip and enjoy the wonderful aroma of good coffee!
Saying this, if you’re at TechReady AND you’re interested in PowerPivot or Analysis Services, let me remind you all of some great presentations … oh, okay – presentations made by me that I hope are great! 
- BIN304: PowerPivot to SAP Connectivity [7/26 3:00-4:15pm, TCC 101] – As the title says it, PowerPivot and SAP together (sort of)!.
- BIN402: Analysis Services Enterprise Case Studies [7/27 10:00-11:15am, TCC 202] – The return of the same titled session from the previous TechReady, this session dives into some of the themes surrounding how to create robust enterprise Analysis Services environments.
- Ask the Experts [7/28 7:00-8:00pm, Hall 4AB]: While the actual Ask the Expert will be 7-9pm, I will need to leave at 8:00pm. Nevertheless, if you have some questions on PowerPivot or SSAS, go for it!
- BIN403: PowerPivot for SharePoint Power Hour…so powerful its 75min [7/30 10:30-11:45am, Sheraton Metro B] – Dave Wickert and I are back to dive in and provide you the insider details and best practices surrounding PowerPivot for SharePoint.
Hope to see you at TechReady! And if you are at TechReady but not interested in PowerPivot or Analysis Services, you can at least get some dang good coffee, eh?!
Ciao!
Posted in Analysis Services, PowerPivot | Tagged Internals, Miscellaneous | Leave a Comment »
June 18, 2010 by dennyglee
Working with my compatriots Ayad Shammout (@aashammout) and Peter Myers, we had found out that PowerPivot at times can be really insensitive…in terms of case that is. That is by default, the collation for PowerPivot is case-insensitive.
For example, if you import the table below:
Within the PowerPivot window, you’ll notice that the lower case “a” has now become an upper case “A”.
If “a” had come before “A”, then both rows would be “a” instead of “A” 
Why are you so in-sensitive?
Actually, it isn’t THAT in-sensitive. When importing, the PowerPivot client picks up the regional settings and sets the database language and engine defaults the collation based on that language (thanks to Deva Kaladipet Muthukumarasamy for clarifying this).
The technical reason behind this had to do with the great compression that you get with PowerPivot’s VertiPaq Column-based store. For example, if you a data source that has the rows:
And your regional settings are set to case insensitive, then you would certainly save more space and compress better if what was stored was just “PowerPivot” (the appropriate branding name) instead of “Powerpivot” which is wrong branding-wise anyways 
Some additional thoughts
One way to work around this is to build a view that would map the different codes so that “a” is “something” and “A” is something else. You could also add modify it slightly such as “a.” so you keep the lower case. The PowerPivot development team is also considering allowing users to change this in the future.
This shouldn’t be a major hassle since there is a workaround and I’ve yet to see a lot of people run into this issue. Saying this, if it is a significant problem – I’d like to hear it in the comments section, eh?!
Thanks!
Posted in PowerPivot | Tagged Loading, Excel, workbook, FAQ | 1 Comment »
June 6, 2010 by dennyglee
As you may already know from some of the tweets concerning #PowerPivot, the book Microsoft PowerPivot for Excel and SharePoint is now available at Amazon.com. The book’s authors are Sivakumar Harinath, Ron Pihlgren, and Denny Guang-Yeu Lee (i.e. myself). Siva and Ron are both long time (this is a good thing) testers with the Analysis Services team. And myself, well – you already know who I am (just in case you don’t, I’m with SQLCAT specializing in Analysis Services and PowerPivot).
This is an end-to-end book describing all of the moving parts within PowerPivot. It is written in difficulty order – that is, each chapter is written corresponding from the novice to more advanced concepts. We cover all the aspects of Excel and DAX – but we also cover the many moving parts concerning SharePoint including: PowerPivot for SharePoint, Excel Services, Geneva / Claims Token, and Troubleshooting.
We cover a lot of stuff in this book – so much so that by the time you finish reviewing it, it may actually be possible to understand our vaunted PowerPivot Client/Server Architecture!
I certainly hope you enjoy it!
Happy Reading!
Posted in PowerPivot | Tagged Book | 1 Comment »
June 4, 2010 by dennyglee
There are many reasons to go to TechEd North America 2010 in New Orleans this year. After all, it is THE event for developers and IT Professionals. And if you want to know more about PowerPivot, here’s yet another reason why you need to go.
There will be a raffle to win a signed copy of the PowerPivot Architecture Technical Diagram (screenshot below) in each of five PowerPivot sessions (five diagrams in total). The sessions are:
- BIE04-INT – Building Custom Extensions to the PowerPivot Management Dashboard (Mon 6/7 1:00pm-2:15pm, Room 241)
- BIC06-INT – SQLCAT: PowerPivot Best Practices and Enterprise Case Studies (Mon 6/7 4:30pm-5:45pm, Room 241)
- BIE22-INT – Tips and Tricks for Troubleshooting a Microsoft SQL Server PowerPivot for SharePoint Installation (Wedn 6/9 8:00am-9:15am, Room 241)
- BIE401 – Deep Dive on PowerPivot Technologies (Wedn 6/9 1:30pm-2:45pm, Room 238)
- BIE303 – Deploying and Managing PowerPivot for SharePoint 2010 (Thurs 6/10 9:45am-11:00am, Room 252)
Just attend one of these sessions our very own PowerPivotGeek Dave Wickert will enter you into a raffle for a full size 44” x 34” signed copy of our PowerPivot Client/Server Architecture poster.
Brought to you by SQLCAT, Analysis Services Team, and our friends at SQL Marketing!
Posted in PowerPivot | Tagged Design | Leave a Comment »
A long time ago, but not in a galaxy too far away…actually, just in Redmond, WA,… there was A NEW HOPE for easier PowerPivot for SharePoint installations! 
![clip_image001[5]](http://dennyglee.files.wordpress.com/2010/08/clip_image0015.jpg "clip_image001[5]")
![clip_image001[7]](http://dennyglee.files.wordpress.com/2010/08/clip_image0017.jpg "clip_image001[7]")
