Using an Excel List or Power BI list to Filter Pyramid Report
Hello,
I have a Pyramid dashboard built out that pulls a report every week and I am currently manually removing lines that are not needed by doing a VLOOKUP between the pyramid export and the excel list (that I pull from power bi) that has a 110,000 item codes that need to be included on the report. If the item codes are not in that 110,000 excel list, I remove them from the pyramid export. This list changes monthly.
My question is, is there a way to create a filter that uses an outside source list in excel or power bi so I can filter this list before even exporting it from Pyramid?
Thanks,
Andy
3 replies
-
Your approach does sound cumbersome! To give you a better approach, please explain a little more about your data and setup. Like:
- What data source does the Pyramid report operate off?
- What data source does the Power-BI report operate off?
Assuming we can get some of the details sorted out, you should be able to simplify and automate this exercise without the dreaded VLOOKUP exercise.
-
There are at least 2 ways to do this:
1. Short to the Point: Open up Model Pro, create a data flow. The first source item should be a "Pyramid Content" item, where you have picked your Pyramid report you normally export. The second source item is either the Excel Export from Power BI (only a small improvement over your current flow) or better a connection to your data warehouse like SQL Server (a significantly better improvement), where you run a query that extracts the listing you used to do in Power BI. Use the "JOIN" operator to fuse the 2 data sets, joining on the same field you did the VLOOKUP on. Last step, use "Excel" as a source and spit the result out to an Excel file. You can save this and schedule it to run automatically and NEVER do it manually ever again.
2. Longer, more powerful: Like #1, open up Model Pro, create a data flow. Again, the first source item should be a "Pyramid Content" item, where you have picked your Pyramid report you normally export. Again, the second source item is either the Excel Export from Power BI (only a small improvement over your current flow) or better a connection to your data warehouse like SQL Server (a significantly better improvement), where you run a query that extracts the listing you used to do in Power BI. Take the 2 datasets and fuse them into a new mini database (use Pyramid's In-memory DB for example). Then switch to the semantic model builder, and add a join relationship on the same field you did the VLOOKUP on. Process and open the resulting model. Now you can slice and dice it and do other things with the entire data set, which is more powerful and smarter overall etc. And of course, you can build a new subscription or publication on the original query result you wanted, sending yourself (or others) the Excel extract automatically from a schedule and NEVER do it manually ever again.
The weakness with #1, is that you need to get the Excel output and it leaves you with little flexibility. The weakness with #2 is that you will need rights to do what you need. I would go with #2 overall, the automation in the end is far superior.
PS: One alternative to the above, is to switch the Pyramid content node with an "MS OLAP" node (only Pyramid 2023 I believe) and write the MDX to extract the query result and continue with either approach.