Post reply

Top 5 things you MUST know about PowerPivot for Excel

  • January 11, 2015 at 2:48 am

    #293633

    Comments posted to this topic are about the item Top 5 things you MUST know about PowerPivot for Excel

  • January 12, 2015 at 12:58 am

    #1769414

    A side topic. How did you get the google search data? Is it republishable? Can you automate the collection of it?

  • January 12, 2015 at 1:17 am

    #1769418

    It may help to spell out what DAX stands for (unless the full name is indeed DAX).

  • January 12, 2015 at 3:44 am

    #1769431

    Google says: Data Analysis eXxpressions (DAX)

  • January 12, 2015 at 4:19 am

    #1769436

    I do tend to think that if it's important enough for you to want to analyse it in depth, it's important enough for you to learn how to do that...

    Graphical dummy interfaces are all very well, but they do hide the underlying fundamentals pretty thoroughly.

  • January 12, 2015 at 6:57 am

    #1769485

    Thanks a lot for sharing.

    You mentioned that "PowerPivot uses the same engine that MS Analysis Services (SSAS) Tabular uses and PowerPivot data sits inside that engine". However, my computer does not have SQL server installed although

    I can access a remote computer which does have 2088 R2 SQLSERVER installed, How come I still can use PowerPivot?

    Thanks.

  • January 12, 2015 at 7:10 am

    #1769491

    When you get the PowerPivot add-in, the Tabular engine comes inside it. You do not need to install Analysis Services or SQL Server. That is the beauty of it. You get the Analysis Services engine running inside the excel process.

  • January 12, 2015 at 8:07 am

    #1769518

    I am the BI go-to guy here where I work (medium sized dental clinic, about 275 employees, about 90% clinical staff). With that said, I do work closely with the business owners and leaders for reporting. I have been using Powerpivot now for just under a year, learning DAX as I develop reports with Powerpivot. Marco Russo's book has been extremely valuable.

    Firstly, I'll say that reusing a tabular data model (using DAX) in SSAS is convenient. I have used Visual Studio SSDT to create tabular data models to upload to SSAS, just as you would with Powerpivot, but can use that data model for other reports as well. Powerpivot can be used to pull directly from an RDBMS and other sources like you said, to build a data model for that report. The source data isn't the cleanest, but it will do the job. The downside of that is that you cannot "reuse" that data model; it is basically embedded into that Excel Powerpivot. It would be nice to have the direction go both ways; from SSAS to Excel, and from Powerpivot to SSAS. Finally, I did look into SharePoint and it is just simply way too costly for just Powerpivot reports. It would be extremely convenient and I would love to have it, but not at the price. However, we are looking into Power BI for Office 365 instead.

    Finally, I have tried to get the other users here to work with Powerpivot, and that has not gone very well. This leads me to question the definition of "Power User". To begin with, we may want to define that better. Power User of what? Excel? Doubt it. That is what the other users here are that I refer to and they not only are reluctant, but I don't think capable of thinking in terms of data like myself. Power User of Data? That's closer. Is the finance guy going to learn DAX? I had Excel 2013 installed on his computer initially for him to view the reports that I create in Powerpivot, and he is even afraid of trying to figure out slicers (He's coming from Excel 2007).

    I think it all comes down to whether you want to learn something new and spend the time getting there or not. If data wasn't my job here, I doubt that I would spend time learning to create reports with Powerpivot. However, I do think I would spend the time trying to actually use the reports and dashboards created from Powerpivot. From what I've seen, even that's too much for the users I work with, even if they really like the results (and they do, when I show them). I would be interested to see what everyone else's experience as/working with "Power Users" is compared to mine.

  • January 12, 2015 at 2:23 pm

    #1769642

    The google search data was obtained from Google adwords. It is available free of cost to anyone with a Adwords account. I'm sure you could do some scraping and automate collection of that data. Haven't really tried to figure that out.

  • January 12, 2015 at 2:28 pm

    #1769645

    Nice article, thanks for sharing.

    I'm in an environment where record level access needs to be tightly controlled. For example if I were to use for revenue vs plan reporting I'd need to ensure each user could only see data for their respective territory. Is there a method to do this with Power Pivot?

  • January 12, 2015 at 2:57 pm

    #1769649

    Currently, the only way to accomplish this in PowerPivot is by creating a separate model for each user group. If security is a concern then you should look into SSAS Tabular. However, you can start with PowerPivot with one user group, test out the model, and then when you are ready, import that PowerPivot model in SSAS tabular. The import from PowerPivot into SSAS is fairly seamless.

  • January 13, 2015 at 7:11 am

    #1769804

    0. Power Pivot is only available with certain Office 2013 Licenses

    [/b]

    http://sqlblog.com/blogs/marco_russo/archive/2013/02/18/the-right-version-of-excel-2013-for-using-powerpivot-powerpivot-excel.aspx

  • January 13, 2015 at 8:39 am

    #1769840

    That is correct. It is not available with all excel versions. However, it is now included with the standalone version which anyone can buy from Amazon. Thanks for clarifying.

  • January 15, 2015 at 8:22 am

    #1770482

    I agree with bwild that learning DAX "all comes down to whether you want to learn something new and spend the time getting there or not". I am also the BI go-to person where I work (Computer HW Mfg with about 400 employees). While we definitely have a lot of users like he describes that are scared to use slicers (and have struggled with filters), we also have a small but healthy contingent of people that do a ton of their own data dredging. This crew understands the significant ROI for themselves and their groups of learning formulas and functions in Excel, and I am confident they would feel the same about DAX. Giving them access to a tool like PowerPivot helps:

    1) reduce the bottleneck for getting information to the business when it's needed

    2) allow functional experts within their respective departments to organize the data... they work with it all the time and know it better than anyone else

    3) spec solutions if they do turn out to be needed by a broader audience, streamlining the process to get information into our enterprise BI space

    Great article!! Very helpful as we continue working to support our company as effectively as possible with minimal "official BI" resources.

  • January 15, 2015 at 8:36 am

    #1770487

    That is so, true. Well said jennym!

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply