|With Microsoft’s focus on Business Intelligence in Excel and with Power BI Designer(www.powerbi.com), the most powerful tool in the toolshed that sets PowerBI aside from competition is Power Query.
It can read data from virtually anywhere. From structured data sources like SQL Server, SSAS, Oracle, MySQL, Access, Oracle and DB2 to JSON/Odata, Facebook, SalesForce, CSV, Excel and Active Directory. It can also read whole folders of files, like a hive.
Power Query is more than a query tool, it’s a fully fledged ELT(popular version of ETL) tool. You can Extract, Load then Transform to Power Pivot, and even to a SQL Server with an Excel Add-in. This is extremely powerful in enabling self service BI.
However, there is no way to schedule Power Query in an efficient way; you currently have to manually kick off the jobs.
But Wait – There is a solution!!! Power Update from the PowerPivotPro team enables you to schedule Power Query Excel Workbooks. Download a free version of this popular tool.
This blog article from Rob Collie explains how it works.
Check it out.
|Excel – Power Query and Power Update
If you are excited about Power Pivot and the announcements Microsoft made last week about Power BI, the introduction of Power Update will make your job even easier and enable updates of Power Pivot models no matter where you publish them. Read more about Rob Collie’s review of Power Update…
Power Pivot now supports write-back. If the company has invested extensively in Power Pivot you are sitting on a gold-mine.. Most budgeting and forecasting solutions costs a gold mine to implement. You can take advantage of your investment by just checking the box in Power Planner, and start the forecasting process right now.
Install Power Planner
Go to the Power Planner website and install the trial solution.
Enable Write Back in Excel Power Pivot
If your Power Pivot is built from a SQL Database, you can just login, share, and start forecasting with the speed and usability of Power Pivot.
Share and Enable Powerful Budgeting and Forecasting features
Power Pivot has
Budgeting, Forecast and Planning Features
|Product and Sales Planning||Power Planner (PP) has a robust feature set for the Business Units like the sales or marketing team to model sales forecast by SKU, Client, Quantity, Discount or Price by Unit. Users can drive numbers with allocations from prior year sales, and change top-level targets for easy automated allocations.|
|Cost of Goods Sold||COGS calculations can be derived from sales numbers based on model calculations or Excel Formulas. Standard Costs and Theoretical Cost of Materials can be modeled with Excel formulas, and updated in the model for easy reporting and Gross Profit analysis.|
|Selling, General & Administrative||Business Trips, IT Spending, Office and Legal Expenses are easily added with comments by contributors. The Line Item Detail and commenting feature makes it easy to get a picture of discretionary spending.|
|Employee and Onboarding||By uploading the company roster, salaries and benefits to Excel, Power Planner proposes a new Employee Expense Budget or Forecast Scenario. By applying simple formulas like ‘inc10%’, an employee’s salary will increase by 10%. Employment and withholding taxes are automatically transferred to the SGA budget.|
|Balance Sheet||Balance Sheet projections are derived from the P&L with simple rules describing the relationship of Accounts Receivable/Payable, Cash, Retained Earnings and Short-Term Debt. Other items like Assets and Depreciation can be modified in the BS directly in order to get a complete overview over corporate performance and funding requirements.|
|Cash Flow Impact Analysis||In order to analyze impact from Operations, Investing and Financing Activities, the integrated Cash Flow section of Power Planner provides powerful insight into the effects or projected operations and the financing activities required to keep a ‘going concern’.|
|Spreading, Allocation and Overhead||Taxes, depreciations and other variable driver based spending is built into the model with Excel based calculations, or more advanced DAX modeling language.|
|Forecasts||With virtually unlimited budget and forecast versions, PP empowers the model to handle an automated 12 month rolling forecast, with the ability to compare versions back in time to analyze what has changed.|
|Goal Seeking||Power Planner’s unique Goal Seeking features integrates all key modules; Product, Employee, SGA, P&L, Balance Sheet and Cash Flow in order to set targets, and analyze the impact on operations. I.e. our clients analyze impact in Net Profit by seeking to adjust quantities sold.|
Power Pivot Features
|Power Pivot Write Back||Power Update enables any user to edit any number in Pivot Tables, add comments, spread and allocate figures in Excel, and update the data warehouse based on role based access rights. Power Pivot tables in Excel is still the de facto #1 choice for Business Analysts and Decision Makers to analyze, slice, and dice corporate data. Power Planner enables role based collaboration in the corporate tool of choice by analyzing and testing out different scenarios.The workbook can be published on a Shared Drive, Share Point on premise, SSAS Tabular model, or Office 365.|
|Security||Power Planner adds a separate layer of user-, or role based security in Power Pivot for Excel, SharePoint or Tabular models. PP also manage what attributes, products, clients, or cost centers users have access to, even in Power Pivot for Excel|
|Adding and Editing New Members||The Adding and Editing New Member features, allows users with proper access right to add or edit members from the DW in Excel. In forecasting and budgeting processes, sales people may add new products that will be used in the new forecast for planning of product launches and new product groups.|
|Modifying SQL Tables – Master Data Management||Additionally you may add new columns and control data types of columns in the DW. For smaller companies this feature can substitute the need for a Master Data Management feature by editing and updating SQL data in Excel.|
|Copy and Shifting Data||Power Planner has a complete feature set for copying member from one scenario to the other or copy actual performance data from clients and turn them into new forecasts in the model. The “Shift Data Feature” enables data to be moved from a calendar year, month, or date into a new time period.|
|Commenting||Traditional Power Pivot tables does not support commenting, but with Power Planner comments are saved in the database. This means they will always show up when users are looking at the same intersection or tuple of the DW.|
|Web Service||PP WebSevice makes any Excel PowerPivot Model into a server, so any user may access the Power Pivot in multi-user mode.|
|Model Versioning||When using Power Pivot for Excel, users may be required to make changes to the model itself. If the model is distributed among many users, the same changes have to be manually enabled in each and every model. With Power Planner, a model is saved to the SQL server, and users get prompted to update their Excel Workbook when they open an older model but the last published version. This ensures consistencies, and is a time saver for shared models.|
|Office 365 and Power BI||Power Planner uses Excel Services to render models on the web. Users have access to all the same features on the web as they have in the thick client of Excel.|
|SharePoint on-Premise||Power Planner enables complex workbooks in SharePoint to be shared on the intranet on premise.|
|SharePoint Online – Office 365||For companies that are deploying or migrating to O365, Power Planner is a certified app that co-exists in the Office 365 models. Power Planner may be deployed in a Windows Azure hosted environment with Power BI with the thick client of Excel or Excel Services(Web)|
|Power Pivot and Data Modeling||Different from most Business Performance Management solutions, Power Planner takes advantage of the investment already made in the Corporate Data Warehouse. There is no need to adopt to a new and rigid template data model. PP is already setup to take advantage of your tabular model in Excel, SharePoint or SSAS. The budgeting solutions comes to you, you don’t have to build it the way the vendor tells you!|
|ERP Integrations||Power Planner has quick start models for:· Microsoft:o Great Plains
· MAS 90 and 500
When Power Query was launched as part of Power BI in February of ’14, the BI community applauded the flexibility and power that Power Query represented. With the current version of Power Query, data enthusiast can collect and refresh information from virtually any data source into Power Pivot.
As the product evolves, we get to see more sophisticated users and they are polarizing into:
- Data User
- The Intermediate
- The King of DAX
The intended use of Power Query was to pull data from virtually any data source ranging from Active Directory to Salesforce and Facebook to Power Pivot as a self-service tool. This is fantastic self-service functionality for most users.
Additionally, the “Intermediate” and “King of DAX” users see the value of loading Facebook, Salesforce, ERP, Active Directory, oData, Web Tables not only to a Power Pivot on their desktop, but all the way to the SQL Server Enterprise Data Warehouse. More specifically; the ODS.(Operational Data Store)
With Power Update from Power Planner, Power Query becomes a complete ETL solution where you really can do Self-Service ETL and not only Self Service BI. The best example is loading Salesforce data into PowerPivot and then further loading this data into the Enterprise DW, without a separate ETL process in SSIS. All data is loaded straight from Power Query, and brings another dimension into Self Service BI. All Power Query and Power Pivot jobs can be scheduled to run around the clock.
Contact us today for a demo version.
Add new members on the fly.
In the Power Planner Ribbon, you can open any SQL table, edit the members, add new products on the fly, and reprocess the model.
- Just click ‘Open’
Select the table to edit. New members are automatically added to the Budget model.
Click Insert Row, and Save the table and the model is regenerated.
Any Pivot Table you can now turn into a Budgeting, Forecasting or Planning solution!
- Get Started:
- Install Power Planner
- Enable Cell Editing in the Pivot Table
- Budget and Forecast on any new or existing model
– Multi User Budgeting and Forecasting Features
– Goal Seeking on any number
– Even and Seasonal Allocations
– Revenue Forecasting
– HR Budgeting
– Capital Expenditure
– Driver based planning
– Collaboration and commenting
Go to our website for more information.
Enable any Pivot Table as your Budgeting Solution
Excel Pivot Tables are still by far the preferred analytics tool for corporate America. A lot of companies have invested heavily in SQL and Pivot Tables.
Make it a Budget System:
With Power Pivot you can make your Pivot Solution into a budgeting solution with the click of a button: Here is how to get started:
- Install Power Planner
- Log on to the Pivot Table
- Click Enable Editing.
Voila – you can now use your Excel Pivot Tables as a Budgeting System from inside Excel for everybody in your organization.
We provide pre-built solutions for Microsoft Dynamics GP/AX/NAV, MAS 500, Salesforce Data.