Access Devs For PowerPivot

I bet everyone who comes here can agree on one thing .. that DAX is not intuitive for the traditional Excel user/dev (?).  Well, I too I was fighting DAX, and then one day I took out my Excel brain and put in my Access brain and tah dah !!  DAX made sense. In fact DAX became almost easy, except for those really out there “tricks” that mostly don’t seem to apply to anything I would REALLY do anyway.  But we’ll see about that I guess.

:-)

I must admit that I was lucky to have experience with OLAP that goes back sporadically for over 20+ years. Also, as I have mentioned here before, I have been doing data-driven spreadsheets including Pivot Tables for nearly 30 years.  But it turns out that it was my experience with Microsoft Access that REALLY gave me a leg up on PowerPivot and DAX. Understanding relational database concepts was the keystone of that learning.

Even moreso than my Access experience helping me with DAX  was how, thanks to Access, I was able to intuitively understand relationships and especially the power of what I call “pre-aggregation” to “massage” or transform my source data into the proper Star Schema format appropriate for use in PowerPivot.  I have been criticized for being “obsessed” with the data more than the cool front end stuff in PowerPivot (DAX) but IMHO the front end means nothing (except a good demo) without a solid backend good data over and over and over again.

So here’s the deal !!  

1. If you’re only going to use Excel as a workspace for the creation of PowerPivot-driven Pivot Tables for use in SharePoint I believe that a background in Microsoft Access is more useful than anything you’ve ever learned in Excel.

2. If you are an Excel Pro who wants to get into PowerPivot, but who doesn’t have a background in relational databases, I suggest STRONGLY that you reach out to that Access guy/gal in your office and become a team to develop PowerPivot solutions (to your mutual benefit).

3. Even better, you should consider seeking out an independent Microsoft Access developer and pay that person a premium to get your data together for your PowerPivot work.  They will be able to easily and quickly clean up your data structures or at least tell you what sources are or aren’t going to be reliable for you.  You may also be surprised at what that guy/gal can help you with far beyond PowerPivot and DAX.

4. If you are an Access developer you MUST GET INTO POWERPIVOT  !! For the last few years ALL of my reports for my Access solution have actually been “dumps” to Excel. Now I am integrating PowerPivot into these “reports” from your databases I definitely add value beyond anything I could ever imagine before.  It is a great addition to your offerings.

Just a thought…

Dick

p.s. I see a lot of “lurkers” here … please feel free to add any comments you might have.  I’m curious what you think of my musings ….  If you’re going to get personal just email me at dick@plogic.ca BTW ;-)

About Biggus Dickus

Dick is a consultant in London, ON Canada who specializes in Microsoft Excel and Microsoft Office Development.
This entry was posted in Access, Excel 2013, Microsoft Access, Microsoft Access 2013, Microsoft Excel 2013, Office 2013, PowerPivot, VBA. Bookmark the permalink.

3 Responses to Access Devs For PowerPivot

  1. Hi Dick,
    let me say first that I really like your articles.
    I’m an Access Developer. And yes I really think that Power Pivot/BI are a great way for us Access Developers to add some cool new technology to our repertory. And right, almost every report I made in the last years had to have an option “Export to Excel”. Besides that many of my customers are finance or controlling guys so they need to have everything in Excel anyway.

  2. Pingback: Access Devs For PowerPivot | MS Excel | Power Pivot | DAX

    • Biggus Dickus says:

      Hey Anthony … Thanx for your comment.

      I am not in a place right now to look at the exact syntax to use for your stord procedures but I can’t help but think it would be as simple as setting up a Conenction using the Data Connection Wizard to SQL Server (yes REAL devs don’t use Wizards I know, I know – but this works ;-) ).

      Then I suggest you connect to your server and database and then choose SQL for your commandtext and then just type your sp and whatever parameters after … like proc1 var1, var2, var3 .. I would think you’d have to use single quotes for your strings and for your dates ….

      Let me know how it goes and if you still got issues I’ll look at it in more detail…

      Dick

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s