Beginners guide to PowerPivot data models RRS feed

  • Question

  • Hi,

    I've been using PowerPivot for a little while now but have finally given into the fact that my lack of knowledge about data modelling is causing me all kinds of problems.

    I'm looking for recommendations on where I should start learning about data modelling for Powerpivot (and other software e.g. Tablea, Chartio etc). By data modelling I mean how I should best organise all the data that I want to analyse which is coming fomr multiple sources. In my case my primary sources right now are:

    • Our main MySQL database
    • Google Analytics Data
    • Google Adwords data
    • MailChimp data
    • Various excels

    I have bought two books - "Dax Formulas for PowerPivot" which is great but sparse on data modelling information and "Microsoft Excel 2013 - Building Data Models with PowerPivot" which looks excellent but starts of at I believe too advanced a level.

    Where should a beginner with no experience of data modelling, but intermediate/advanced experience of Excel go to learn skills for PowerPivot Data modelling?

    By far the main issues is that our MySQL databases are expansive and include hundreds of tables across multiple databases and we need to be able to utilise data from all of them. I imagine that I somehow need to come up with a intermediary layer between the Databases and Powerpivot which extracts and flattens the main data into fewer more important tables, but would have no idea how to do this.

    Also to be clear, I am not looking at ways of modelling the MySQL database itself - our developers are happy with the database relationships etc, it just the modelling of that data within PowerPivot and how to best import that data.

    Recommendations would be absolutely brilliant, its a fantastic product but right now I'm struggling to make the most of it.

    • Edited by Maracles Friday, September 12, 2014 11:43 AM
    Friday, September 12, 2014 11:23 AM


All replies

    • Edited by Mark Weisman Friday, September 12, 2014 12:46 PM
    • Proposed as answer by Michael Amadi Sunday, September 14, 2014 11:26 AM
    • Marked as answer by Charlie Liao Sunday, September 28, 2014 3:05 AM
    Friday, September 12, 2014 12:45 PM
  • Thanks for the recommendations, I am aware of the last two of those and http://www.powerpivotpro.com/ in particular has proved very useful (TechNet less so). 

    I will take a look at SQLBI in more detail but from a very casual browse it seems like this too is targeted more at experienced users. There paid courses may definitely prove useful though.

    I think what I'm getting at is that there are probably an increasing number of people like myself who have fallen into PowerPivot without a traditional background in databases and data modelling. In my case I have a small business of 15 employees and we were using Excel and PivotTables to do some basic analysis before soon discovering that our data was too complicated and that I needed something. PowerPivot definitely seems to solve that issue and I'm having much better success now than I was without. I also feel quite competent with DAX and actually building tables from the PowerPivot data model.

    What I'm lacking in is the very first step of cleaning and preparing raw data for import and then importing it into Powerpivot and setting up a efficient model. I have to be honest that your links above did bring PowerQuery to my attention and it seems like a brilliant tool and one of the missing links. I would however still like to see a beginners guide to data import and model set-up as I don't think I've yet come across one either in book or online form which explains the fundamentals well.

    Tuesday, September 16, 2014 10:11 AM