Thanks Thanks:  0
Likes Likes:  0
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 23

Thread: Adding Power Pivot Data to the Data Model?

  1. #11
    Board Regular
    Join Date
    Jan 2014
    Location
    Maine
    Posts
    155
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Adding Power Pivot Data to the Data Model?


    Adding Power Pivot Data to the Data Model?
    macfuller,
    The project involves a large group of cooperating organizations, State and Non-profits, that are collaborating on various aspects of this. I am just one person working for a small non-profit that is assisting in handling certain aspects of the data. With everyone knowing who plays what roles and why, which is beyond me, it is on our plate, so I am not asking questions, just proceeding. It is one of our roles to assist with the regional summaries for regional coordinators and to bring the processed summaries back into ArcMap for visual displays and monitoring progress. In the conservation world, everyone's plate is full, so while there may be data specialists who could help somewhere, their plates may be full with other tasks. I have made great progress in streamlining, but I am always trying to discover more. With infinite time and resources, this would be easier, but with time constraints, I learn a little more between each summary where I can streamline a little more, but then move on to get the results. Learning on the job per se. This is a four year project, with monthly downloads and various analyses depending on the season, with the data set getting larger every month, so perhaps I will get to the point where it could be a "click refresh" and there you have it, but I am not there yet. Thanks for the advice/input, and I loved the analogy you provided!
    Best Wishes,
    Maggie

  2. #12
    Board Regular
    Join Date
    Jan 2014
    Location
    Maine
    Posts
    155
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Adding Power Pivot Data to the Data Model?

    Oh Bother!
    I clicked to get the wrong link, that was another forum question. SOOOO sorry!
    https://app.box.com/s/e17s0dfrouk8w4t147499q37vivzmzve
    Maggie

  3. #13
    Board Regular
    Join Date
    Jan 2012
    Posts
    831
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Adding Power Pivot Data to the Data Model?

    Are you saying you create that report from the data fields and the locations tables? If so, what do you have already, and how, and what do you still need to automate?

  4. #14
    Board Regular
    Join Date
    Jan 2014
    Location
    Maine
    Posts
    155
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Adding Power Pivot Data to the Data Model?

    theBardd,
    I create that report, but much of it is manual, filling columns from different pivots, and in some cases, taking counts from formulas I manually enter for columns within some pivots to populate cells in the report header. Messy sounding, I know, but I have not figured a way to link those cells to values in pivot tables. Then, in some cases, I can automate some of the cell data in the report header with formulas based on the data within the report sheet. As I said, I can generate what I need, but I am having difficulty in learning how to create that report so it can just be "refreshed". Perhaps there are report builder tools that I know nothing about, which is likely the case, but I am learning as I go. I do not want to waste your time, and I truly appreciate your interest and effort in trying to help, but I am not sure that, without being able to share the data, I can provide enough clarity for you to really explain it enough. I will keep "playing" as things progress, and hopefully I will get there.
    Thank you,
    Maggie

  5. #15
    Board Regular
    Join Date
    Jan 2012
    Posts
    831
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Adding Power Pivot Data to the Data Model?

    Do you need to see every Site in the report, or would it be okay to have a slicer with sites on it and just view a selected site at a time?


    What I 'm thinking I cube formule, but if we were to show all species against every site, I think I might get very slow, but if we were jut to show one and use th data model to handle switching between sites that might be more manageable. If you needed them all it would be a trivial task to select one at a time on a worker sheet and copy/paste values to a final report sheet.


    Are those two tables examples of what you get or are there more. I would need a site table ad maybe more, some I could get from that data, such as sites, but are there others needed?

  6. #16
    Board Regular
    Join Date
    Jan 2012
    Posts
    831
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Adding Power Pivot Data to the Data Model?

    Thinking about this some more, how about just using VBA to populate the final report from your pivot results?

  7. #17
    Board Regular
    Join Date
    Jan 2014
    Location
    Maine
    Posts
    155
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Adding Power Pivot Data to the Data Model?

    theBarrd,
    You are so kind to keep exploring this with me. The "data" sheet is a snap of some made up data, not the full data. I had watched a tutorial on pivot techniques by Chris Dutton, which showed me a lot about slicers and filters for interactive report development. But I have yet to attempt to take my report to that interactive level. Upon my searching online for how to get pivot data into a "model" in attempts to create links in a "form" to its results, I stumbled across Cube concepts, and even for fun, copied one of my pivots to another sheet, and went into olap tools and converted to formulas to investigate...oh bother, I realized that perhaps I will have to learn that one another time. If you know of any good tutorials on such, I would greatly appreciate the tip.
    Thanks again,
    Maggie

  8. #18
    Board Regular
    Join Date
    Jan 2012
    Posts
    831
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Adding Power Pivot Data to the Data Model?

    Quote Originally Posted by Maggie Barr View Post
    . But I have yet to attempt to take my report to that interactive level. Upon my searching online for how to get pivot data into a "model" in attempts to create links in a "form" to its results, I stumbled across Cube concepts, and even for fun, copied one of my pivots to another sheet, and went into olap tools and converted to formulas to investigate...oh bother, I realized that perhaps I will have to learn that one another time. If you know of any good tutorials on such, I would greatly appreciate the tip.
    I do a good conference presentation on that topic (even if Ido say so myself), but unfortunately I have never recoded it and I don't think any of the conferences did either.

  9. #19
    Board Regular
    Join Date
    Jan 2012
    Posts
    831
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Adding Power Pivot Data to the Data Model?

    Accepting that the data is not shareable and the project is far to complex to solve over these forums, I have had another thoiught that might be the simplest solution for you to be able to take it forward.


    I have said many times that I do not understand why you would want to add the pivots back into the datamodel, but assuming that if you did you could work with that and take the project forward, how about this as a solution? If you supply the names of the pivot tables that you want to include, we could write some VBA code that would take those pivots and load them into Excel tables. We could even write code to load them as Power Query queries, but as that is just a once-off, it is probably best that you do it yourself, especially as you would need to work in Power Query to use it anyway. You could add a button to a worksheet to fire the VBA.

  10. #20
    Board Regular
    Join Date
    Jan 2014
    Location
    Maine
    Posts
    155
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Adding Power Pivot Data to the Data Model?

    theBardd,
    I am very happy to inform you that your comment of "load them as Power Query queries" made me go back to looking into this option. I had been able to create reference queries that would show me what I needed, but I was unable to link them through relationships back into the data model to allow for the display of the data from different queries within the same pivot. Well, I figured it out (mostly)! I have a locations table lookup that is unique at the Block Name/Code level, but my analyses actually look at the Region and Block Type, not the block name, so I created a reference query that grouped these so that I could then create the relationship needed, but I also had to create a merged columns of the region; block type so that I could base the relationship on that (one to many) but still allow the links to follow through. That being said, I was still having a hard time getting the two separate queries to put data in by reading the species list accordingly, but then I realized I needed a reference query to the full dataset that created a distinct species list that appears in the data, and then link that to those other two queries based on species, and use that distinct species list as the rows. Now I can create one pivot table that has the columns from what would have been two pivot tables. That in and of itself is huge for me!

    I think I am at the point that I may need to know more about writing measures, if that is the tool to use. I would like to get the percentage of blocks each species is observed in in the pivot as a column. I created yet another reference query that parsed the data down and provides the # of blocks with data for each region; block type. I have the # of Blocks observed in for each species in each region; block type in one query, but I need a column (I think in that query) that will look for the # of blocks with data in another query that matches the region; block type field to do the percentage calculation. I hope this is making some sense, and if it does, I would appreciate any advice.

    Mostly, I just wanted to make sure that I apologized for my delay in responding, and thank you for your continued questioning of why I wanted to do things the way I was, as it was the wrong way. I just had not actually thought/figured out how to manipulate the raw data and lookup data as reference queries to create the relationships between all the queries to enable the pivot I needed. Again, thank you for your persistence that I didn’t need to do what I thought I did, and for all of your input and effort in helping.
    Best Wishes,
    Maggie

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •