Using SQL Server PowerPivot for Excel to Analyze SAP Data

Sorry! I’ve been offline and haven’t blogged or responded to comments for quite some time and frankly I don’t have any good excuses: I fell asleep My dog ate my WordPress login account info (never mind that I don’t have a dog yet) I was working (yeah, right) I used the TARDIS to back in time to the Byzantine empire and became a Roman soldier…oh wait, that’s me confusing the awesome Doctor Who episode “The Pandorica Opens” with reality again Okay, no more excuses and back to work!  I’ll catch up on the comments eventually too! So it’s the new…

Rate this:

Can I access PowerPivot via a data access API?

I recently got asked the question: Does PowerPivot have any data access APIs? Is there any way to to programmatically access the data store?   I figured that a lot of people will eventually ask this question (heck, that’s what I had asked when I had first heard about PowerPivot).  The answer as of 2010 is: Right now, there is no supported way to access the data store within Excel as all PowerPivot for Excel APIs are private.  Saying this, the data store backup is stored within Excel itself as per the posting For Excel PowerPivot, the database is IN…

Rate this:

PowerPivot, you are so insensitive! (case that is)

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…

Rate this:

Pivots, Cube Formulas, Named Sets … Oh my!

Over the last few days, @PowerPivotPro has been calling me out for diving into the deep end into his world of Excel and getting the proverbial egg on my face (Joker picture not-withstanding).  He’s called me out, challenged my geek hood, and sliced / diced my postings (pun intended).  I was going to let this all slide…and then he started using “eh?!” in his postings!!!!  So the gloves are coming off!!!  … Or are they? CubeSet and CubeRankedMember cube formulas are great! For starters, in paying homage to someone who is vastly superior in Excel (I’m sure in other things…

Rate this:

Create a #PowerPivot report filtered by the Top X Users by <Logic> (Part 3 of 3)

Background This blog posting is a continuation of previous post, Create a #PowerPivot report filtered by the Top X Users by (Part 1 of 3), where I had created an Excel Named Set based on the Top 10 Users by Events as noted in Figure 1.  What is handy about using named sets is that it will only render the top 10 rows as opposed to the millions of rows of users this data set has.  While it is easy to show the Top 10 users, it is not straightforward to filter by those Top 10 users because Excel Named…

Rate this:

Create a #PowerPivot report filtered by the Top X Users by <Logic> (Part 2 of 3)

Background This blog posting is a continuation of previous post, Create a #PowerPivot report filtered by the Top X Users by (Part 1 of 3), where I had created an Excel Named Set based on the Top 10 Users by Events as noted in Figure 1.  What is handy about using named sets is that it will only render the top 10 rows as opposed to the millions of rows of users this data set has. Figure 1: Top 10 Users by Events using an Excel  Named Set While it is easy to show the Top 10 users, it is…

Rate this:

Create a #PowerPivot report filtered by the Top X Users by <Logic> (Part 1 of 3)

Background I was recently working with a customer to help them solve an interesting PowerPivot problem where they had wanted to get the Top 10 users within their data set and create reports based on that.  One approach is to create a rank function within PowerPivot as noted in a Rank function thread on the SQL Server 2008 R2 PowerPivot for Excel forum?  Another way is to create a Pivot Table where the UserID was placed in the [rows] and the measure in question in the [data] area; and sort the data from largest to smallest. Figure 1: Events by…

Rate this: