Category: Dashboards

Solution for Power BI Refresh Error in the Web Service

Posted on by Bb

Web Service Power BI Refresh Error When Trying to Refresh On Premises 32-bit Database/Source

I recently worked with a client who is still using an old 32-bit Oracle instance with lots of dependencies. Upgrading is not currently an option. In her Power BI report/model, she is querying several tables in the Oracle database. Unfortunately due to the 32-bit architecture she was not able to schedule her refreshes, even using the desktop gateway.

The Power BI refresh error she received was “Failed to update data source credentials: An error happened while reading data from the provider: ‘Attempt to load Oracle client libraries threw BadImageFormatException. This problem will occur when running in 64 bit mode with the 32 bit Oracle client components installed.'”

Workaround for Power BI Refresh Error in Web Service When Trying to Refresh On Premises 32-bit
Power BI Architecture Error

As a result, she found she could only refresh this report using the Power BI desktop client. She would then manually publish to the cloud. This was cumbersome as the data refresh took quite some time, and she needed it done daily. I came up with two solutions for her, both of which we implemented and tried. One proved to better for us, and ultimately she is successfully using this first solution/workaround that I will cover.

Solution #1 – Export Query Results to CSV and Post on SharePoint

  • So just as the title of the section states, the final workaround was for us to automate the generation of CSV files to ultimately be consumed by Power Query.
  • We did this by using a combination of scripts pulling data via SQL in VBA (Access, Excel) and via SQL in Python. You can pull the data any way you are most comfortable (macros, etc.).
  • The query results are then outputted to the CSV files, which are written directly to SharePoint folders.
  • After all this was set up and the initial files were changed, we then changed the source of the data in Power Query, to now navigate to SharePoint folders and then process the CSV files.
  • It is important to note that you are not required to use CSV files. These made sense in our case due to the amount of data we were working with (generating several very large files nightly). You can write to any supported file format if that works better in your case, as long as you can place it on SharePoint and Power Query can process it.
  • Also as noted we used a separate folder for each unique query. If you are also replacing several queries, you can either do this (write to separate folders) or if you would prefer just one, then utilize different naming convention patterns to filter.
  • Everything is then kicked off via a command line batch script (.bat) using the Windows task scheduler on her desktop to run overnight. We also set up the same script on a colleague’s PC as a backup/for redundancy in case something were to happen, but it is not actively running.
  • After refreshing and publishing the updated report with the new SharePoint sources, I then went into settings in the Web Service and added the credentials there for SharePoint. I saved and kicked off the refresh in the browser.
  • The refresh was successful, so I then set a daily schedule for the web service to refresh automatically. No intervention has been needed since implementation!

Solution #2 – Automate the Power BI Desktop Refresh Process Using Python

  • This method is much less convenient in my opinion, as after it had been implemented, it failed often and required manual intervention several times before we moved on to the other solution.
  • Another caveat is that this solution requires the user to have at least some familiarity with Python (or finding someone with Python knowledge to tap as a support resource).
  • Also, obviously someone needs to install Python, the required package and then set up the automation to run the scripts.
  • Important note – it is possible to install a portable version of Python if you do not have admin rights on the desktop.
  • The package is called pbixrefresher (repo is linked).
  • This gentleman does a great job of walking through setting it up in his YouTube video about it: https://youtu.be/wtXXpicCUlY

I hope this post has been helpful and has maybe given you some ideas on how to proceed if you or an organization you are supporting is facing this same situation with older data sources. Also, I’d love to hear about other solutions if you’ve faced the same issue and handled it differently.

Please reach out with any questions or if you would like to see further coverage of any of the concepts/steps mentioned in this post. I am also available on a limited basis for consulting as my schedule is full as of late (a good thing!). Just fill out the contact form and I will get back to you.

limiting rows in Power BI

Limiting Rows in Power BI when Creating Reporting for Big Data (Overcoming Memory Limits)

Posted on by Annie D

Are you working with more data than you can handle in Power BI Desktop on your machine? Work with the subset of data locally and all the data in the service without using Premium and overcome the memory limitations in the desktop application!

Watch the video below on limiting rows in Power BI in the desktop, for the step by step instructions and deeper explanation.

To summarize, Patrick is working with a data source that has 600k rows.  He adds a step in the query to keep top rows, then adds a parameter to trigger whether to bring in all rows, or a subset.  He then adds a line to utilize the parameter.  Patrick demonstrates the pull with the parameter set to A (all records), and then again using the S (subset) parameter, which pulls in only 100K records.

Patrick then publishes to the service while only having 100k records loaded and continues on to demonstrate how to change the parameter to A in the browser/published version.

He then manually kicks off the refresh in the service and once complete, shows us that all 600k rows have now been imported into the model.  Very cool and useful trick, especially when working with lots of data, especially Big Data!

Video and Tip Courtesy of Guy In A Cube:

— http://twitter.com/guyinacube
— http://twitter.com/awsaxton
— http://twitter.com/patrickdba
— http://www.facebook.com/guyinacube
— https://www.instagram.com/guyinacube/
— https://guyinacube.com

***Gear***
🛠 Check out their Tools page – https://guyinacube.com/tools/

#PowerBI #PowerQuery #GuyInACube

rank in Access

Rank in Access Query (Sales Ranking)

Posted on by Bb

A common request I get is for ranking of sales in Access by region, state and item combination for analysis and space management.  In my business, we have some overlap, where a state can be part of two separate areas or regions, for example Southern CA vs. Northern CA.  It’s helpful for us to show separate rankings for each region and state combo, as the results often tell a different story for each.

The Pass-Through query below enables me to determine the ranking at each of these levels.  By entering as a Pass-Through query, you can utilize the Rank and Partition SQL commands.  After running this query, I bring the data into Excel for further analysis, but this query is a good starting point for any similar analyses.

Here is the full Access pass-through query, color coded for the explanation below:

Select Region, State, Product_ID, SumSaleQ, StoreCount,Rank
FROM (Select Region, State, Product_ID, SumSaleQ, StoreCount, Rank() over (Partition By state, Region Order by SumSaleQ DESC)as rank
FROM (Select s.Region, s.state, m.Product_ID, sum(m.sale_quantity) as SumSaleQ, count(distinct(m.site_num)) as StoreCount
FROM Store_monthly_sales m, FR_Site_Master s
WHERE m.Site_num = s.Site_num AND m.accrual_month in (‘202007′,’202006′,’202005’)
GROUP BY s.Region,m.Product_ID,s.state)
WHERE SumSaleQ >5)
WHERE rank <205
Order by State ASC, Region Asc, Rank Asc

Explanation

Query 1

Diving right in, since we are measuring at multiple levels, we have some nested queries.  Working from the inside out, my first nested query is this one:

(Select s.Region, s.state, m.Product_ID, sum(m.sale_quantity) as SumSaleQ, count(distinct(m.site_num)) as StoreCount
FROM Franchise.Store_monthly_sales m,Franchise.FR_Site_Master s
WHERE m.Site_num = s.Site_num AND accrual_month in (‘202007′,’202006′,’202005’)
GROUP BY s.Region,m.Product_ID,s.state)

Query 2

We then nest that within this query (the WHERE clause is optional – I am dealing with sales in the thousands, and want to exclude items with a sales quantity less than 5.  Note the Rank() over and Partition By Commands here:

(Select Region, State, Product_ID, SumSaleQ, StoreCount, Rank() over (Partition By state, Region Order by SumSaleQ DESC)as rank

FROM …

WHERE SumSaleQ >5)

Query 3

The final nesting and main query is selecting the rank from the second query, along with the other pertinent attributes.  The WHERE clause is again optional, I am looking to limit my results to the top 200 results only for each region and state combination. I use 205 as the figure to allow for ties.  The Order by is also optional.  I like to order the results by state, region and then rank ascending.

Select Region, State, Product_ID, SumSaleQ, StoreCount,Rank

FROM …

WHERE rank <205
Order by State ASC, Region Asc, Rank Asc

Full Query (no color coding)

Select Region, State, Product_ID, SumSaleQ, StoreCount,Rank
FROM (Select Region, State, Product_ID, SumSaleQ, StoreCount, Rank() over (Partition By state, Region Order by SumSaleQ DESC)as rank
FROM (Select s.Region, s.state, m.Product_ID, sum(m.sale_quantity) as SumSaleQ, count(distinct(m.site_num)) as StoreCount
FROM Store_monthly_sales m, FR_Site_Master s
WHERE m.Site_num = s.Site_num AND m.accrual_month in (‘202007′,’202006′,’202005’)
GROUP BY s.Region,m.Product_ID,s.state)
WHERE SumSaleQ >5)
WHERE rank <205
Order by State ASC, Region Asc, Rank Asc

Conclusion

I hope this post will help you in your efforts to rank in Access.  It can be a bit of a challenge, but hopefully the breakdown of the steps here will help you achieve your ranking goals.  Please comment any questions, feedback, or what you would like to see next!  Share this post if you feel it would help someone else! 🙂

If I helped you, please consider buying me a coffee via PayPal! Thanks!!

Comprehensive Introduction to Excel Power Pivot, DAX Formulas and DAX Functions

Posted on by Annie D

Download Excel START File: https://ift.tt/2FrxeX5
Second Excel Start File: https://ift.tt/2Dtf4Sf
Download Zipped Folder with Text Files: https://ift.tt/2Frxfu7
Download Excel FINISHED File: https://ift.tt/2qSnYkx
Download pdf Notes about Power Query: https://ift.tt/2FrxwgD
Assigned Homework:
Download Excel File with Instructions for Homework: https://ift.tt/2qRom2T
Examples of Finished Homework: https://ift.tt/2Frxgyb

This video teaches everything you need to know about Power Pivot, Data Modeling and building DAX Formulas, including all the gotchas that most Introductory videos do not teach you!!!

Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.

Topics:
(00:15) Introduction & Overview of Topics in Two Hour Video
1. (04:36) Standard PivotTable or Data Model PivotTable?
2. (05:51) Excel Power Pivot & Power BI Desktop?
3. (12:31) Power Query to Extract, Transform and Load Data to Data Model – Get data from Text Files, Relational Database and Excel File.
4. (25:47) Build Relationships
5. (27:43) Introduction to DAX Formulas: Measures & Calculated Columns
6. (29:15) DAX Calculated Column using the DAX Functions, RELATED and ROUND
7. (31:20) Row Context: How DAX Calculated Columns are Calculated: Row Context
8. (33:49) We do not want to use Calculated Column results in PivotTable using Implicit Measures
9. (34:05) DAX Measure to add results from Calculated Column, using DAX SUM Function.
10. (35:29) Number Formatting for DAX Measures
11. (36:35) Data Model PivotTable
12. (39:31) Explicit DAX Formulas rather than Implicit DAX Formulas
13. (41:50) Show Implicit Measures
14. (45:00) Filter Context (First Look) How DAX Measures are Calculated
15. (50:14) Drag Columns from Fact Table or Dimension Table?
16. (53:30) Hiding Columns and Tables from Client Tool
17. (55:52) Use Power Query to Refine Data Model
18. (57:54) SUMX Function (Iterator Function). DAX Measure for Revenue using the SUMX Function to simulate Calculated Columns in DAX Measures
19. (01:01:00) Compare and Contrast Calculated Columns & Measures
20. (01:04:26) Why We Need a Date Table. Why we do NOT use the Automatic Grouping Feature for a Data Model PivotTable
21. (01:06:46) Build an Automatic Date Table in Excel Power Pivot. And then build Relationship.
22. (01:11:00) Introduction to Time Intelligence DAX Functions. See TOTALYTD DAX Function
23. (01:13:47) Introduction to CALCULATE Function: Function that can “see” Data Model and can change the Filter Context. (01:18:00) Also see the ALL and DIVIDE DAX Functions. Create formula for “% of Grand Total”. Also learn about (01:21:30) Context Transition and the Hidden CALCULATE on all Measures.
24. (01:27:18) DAX Formula Benefits.
25. (01:28:00) Example of DAX Formula that is easier to author than if we tried to do it with a Standard Pivot Table or Array Formulas
26. (01:31:50) AVERAGEX Function (Iterator Function) to calculate Average Daily Revenue.
27. (01:34:00) Filter Context (Second Look) AVERAGEX Iterator Function
28. (01:36:16) Build Dashboard. Create multiple DAX Formulas. Create Multiple Data Model PivotTables and a Data Model Chart.
29. (01:38:38) Create Measures for Gross Profit and Gross Profit %
30. (01:41:27) Continue making more Data Model PivotTables.
31. (01:41:50) Make Data Model Pivot Chart.
32. (01:45:10) Conditional Formatting for Data Model PivotTable.
33. (01:46:22) DAX Text Formula for title of Dashboard
34. (01:47:50) CUBE Function to Convert Data Model PivotTable to Excel Spreadsheet Formulas.
35. (01:50:05) Adding New Data and Refreshing.
36. (01:50:40) Update Excel Power Pivot Automatic Date (Calendar) Table. Clue is the blank in the Dimension Table Filter.
37. (01:52:20) How to Double Check that a DAX Formula is yielding the correct answer?
38. (01:53:22) DAX Table Functions. See CALCULATETABLE DAX Function.
39. (01:55:07) DAX Studio to visualize DAX Table Functions, and to efficiently create DAX Formulas
40. (02:00:12) Existing Connections to import data from Data Model into an Excel Sheet
(02:03:15) Summary

View on YouTube

Microsoft Power Tools for Data Analysis: Dashboards & Reports. Class Introduction Video. MSPTDA #01.

Posted on by Annie D

Download Excel & pdf Files: https://ift.tt/2tiUtJK
This video introduces the topics that will be covered in this Highline College BI 348 Class:
Name of Class:
BI 348 – Microsoft Power Tools for Data Analysis:
• Power Query
• Power Pivot
• DAX
• Power BI Desktop
• Excel
For Creating:
• Data Models, Reports, Dashboards and Analytics
Taught by Mike excelisfun Girvin, Excel MVP 2013-2018
• A class about connecting to multiple source of data, transforming the data into a refreshable & dynamic data model, and building reports and dashboards to provide insightful and actionable information.

Prerequisites for this class:
• Busn 216: Excel Basics, https://www.youtube.com/playlist?list=PLrRPvpgDmw0n34OMHeS94epMaX_Y8Tu1k
• Busn 218: Advanced Excel, https://www.youtube.com/playlist?list=PLrRPvpgDmw0lcTfXZV1AYEkeslJJcWNKw
• Busn 210: Business Statistics, https://www.youtube.com/playlist?list=PLrRPvpgDmw0ngx_uPhvasTbOWLOztsaBj

What Version of Excel:
• Office 365 (updated each month)

What Version of Power BI Desktop:
• Free Tool we will download (update each month)

Over View of Topics for the class:
1. Data Analysis / Business Intelligence terms and concepts that we will learn in this class:
• Proper Data Set
• Fact Table
• Dimension Tables
• Relationships
• Star Schema
• ETL
• Measures
• Dashboards
• SQL
• Data Warehousing
 
2. Learn how to use Excel Power Query:
• Import Data from multiple sources
• Clean and Transform Data
• Create Data Components for Star Schema Data Models
• Load Data To Excel, the Data Model and Connection Only
• Replace Complicated Excel Solutions with Power Query Solution
• Use the Power query User Interface to create Power Query Solutions
• Learn about the Case Sensitive, Function-based M Code Language that is behind the scenes in Power Query
3. Learn how to use Excel Power Pivot:
• Excel Power Pivot provides:
i. Data Model where we can have multiple tables, formulas and relationships (Star Schema)
ii. Columnar Database to hold “Big Data” and process quickly over that “Big Data”
iii. New Formula Language called DAX:
1. Many More Calculations than in Standard PivotTable
2. Build One Formula that can work in many reports
3. Add Number Formatting to Formulas
• Excel Power Pivot to:
i. Replace VLOOKUP Formulas and Single Flat PivotTable Data Source with Multiple Tables, Relationships in the Data Model to create more efficient Reports & Dashboards
ii. Use Power Pivot Columnar Database to hold millions of rows of data
iii. DAX formulas have more Power than Standard PivotTable Calculations
4. Learn about Building Star Schema Data Models:
a. Why they are important in Power Pivot and Power BI Desktop
b. How to build them using:
i. Power Query
ii. Power Pivot
iii. DAX
iv. Power BI Desktop
5. Learn how to author DAX Formulas for Excel’s Power Pivot & Power BI Desktop:
a. Calculated Column Formulas for Data Model
b. Measure Formulas for PivotTables
c. DAX Functions like SUMX, CALCULATE, RELATED, and Much More…
d. Lean why we must create Explicit rather than Implicit formulas
e. Learn how Row Context works in formulas
f. Learn how Filter Context works in formulas
g. Learn about Scalar & Table Functions
h. Use DAX Studio to visualize and analyze DAX Formulas
6. Learn how to use Power BI Desktop:
a. Power Query to import, clean, transform and create Star Schema Data Models
b. Create Relationships
c. Create DAX Formulas
d. Build Interactive Visualizations
e. Build Dashboards
 
7. Learn how to use Excel:
• Spreadsheet Formulas & Functions
• Standard PivotTables
• Power Query
• Power Pivot
• Build Data Model PivotTables and the resultant Reports, Dashboards and Analytics
8. Building Refreshable, Insightful Dashboards
a. Build Excel Dashboards
b. Build Power BI Dashboards
9. Case Studies to practice using Power Pivot & Power BI Desktop for Reporting, Building Dashboards and Building Business Analytics Solutions

View on YouTube

Free Excel Education for the World at the excelisfun Channel at YouTube!

Posted on by Annie D

Excelisfun YouTube Channel: https://www.youtube.com/user/ExcelIsFun
Excel Basics Free Class Playlist: https://www.youtube.com/playlist?list=PLrRPvpgDmw0n34OMHeS94epMaX_Y8Tu1k
Advanced Excel Free Class Playlist: https://www.youtube.com/playlist?list=PLrRPvpgDmw0lcTfXZV1AYEkeslJJcWNKw
Excel & Business Math Playlist: https://www.youtube.com/playlist?list=PLrRPvpgDmw0kMy6GdNvsecPSOP5Tc_ydL
Dashboard Playlist: https://www.youtube.com/playlist?list=PLrRPvpgDmw0kYt4c50Sg7BXGLBAjiW6VQ
Power Query Playlist: https://www.youtube.com/playlist?list=PLrRPvpgDmw0m3ohSvgwoHvd0KO8QsQdiK
VLOOKUP Function Playlist:
Everything you need is here at the excelisfun Channel at YouTube:
1. Search for topics
2. Free College Classes
3. Playlists for Your Excel Topics
4. Excel Basics
5. Charts
6. PivotTables
7. Array Formulas
8. Power Query
9. Power Pivot
10. Power BI Desktop
11. Efficiency
12. Compare and Contrast Methods
13. Much more…

View on YouTube

Excel Magic Trick 1449: DAX Measure % Change MOM & YOY Formulas For Incomplete Years

Posted on by Annie D

Excel Magic Trick 1449: Why Standard PivotTable Show Values As % Change Does Not Work for Incomplete Years and How To Fix It with DAX Formulas
Download File:
Start File: http://ift.tt/2uqGsLB
Finished File: http://ift.tt/2vCmsEe
Entire page with all Excel Files for All Videos: http://ift.tt/1kSFWvs
Video shows how to create DAX Formulas for Change & % Change formulas for both Month Over Month and Year Over Year.
Extensive discussion about the hows and whys of how the formula calculates. In particular we discuss how Filter Context and Row Context Work together in SUMX to get the correct amount for each time period.
See how to
1. (00:10) Introduction
2. (00:40) Look at Change and % Change “Show Values As” Standard PivotTable Calculations and why they are not correct when you have incomplete years, like is often the case with Dashboards & Reports.
3. (01:53) First insights about what the DAX formula will be required to doing order to calculate the correct % Change.
4. (03:07) Look at Excel Tables and Data Model and first Measure that have already been created
5. (03:45) Start Data Model PivotTable
6. (04:28) Theory about calculating Sales Last Year
7. (04:43) Discussion about Filter Context and how DAX Measures evaluate / calculate. Discussion and Visuals about how Criteria in Row Area of PivotTable (also known as Filter Context) flows across relationships to create filter Sales Tables that have fewer rows than the original data set so that DAX Formulas can calculate more quickly on Big Data.
8. (05:23) Introduce CALCULATE DAX Function (functions that can change the Filter Context) and SAMEPERIODLASTYEAR DAX Function (generates a valid list of dates from last year’s period).
9. (05:42) First attempt at DAX Measure to calculate “Sales Last Year” using CALCULATE and SAMEPERIODLASTYEAR.
10. (07:11) Diagram of what the SAMPERIODLASTYEAR Function does to the Filter Context Criteria.
11. (07:43) Problem with first attempt at “Sales Last Year” DAX Measure: It shows values for periods that have no current sales, and Total for the last year in data set adds all 12 months from previous period.
12. (08:04) Introduction to SUMX (iterates a formula over a table and then adds the results).
13. (08:54) Second attempt at “Sales Last Year” using SUMX, VALUES and IF DAX Functions
14. (09:15) Introduction to VALUES DAX Function (yields a unique list of items for a column in the Current Filter Context).
15. (10:51) Error from SAMPERIODLASTYEAR and how to use IF and HASONEVALUE DAX Functions to fix it.
16. (12:01) Look at “Sales Last Year” Measure in PivotTable and see how it is yielding the correct answer
17. (12:35) “Sales Lat Year” Formula: Extended discussion with Visuals and Diagrams to understand how VALUES generates a different size table in each cell in the PivotTable and how SUMX iterates over the VALUES generated table to calculate the correct answer in each cell in the PivotTable.
18. (15:42) Problem with “Total Sales” formula when using it to calculate the change between this year and last year.
19. (16:34) “Comparison Total Sales” DAX Formula to calculate correct Total Sales for the Numerator in the “% Change” formula. See the functions, SUMX, VALUES and IF
20. (18:07) DAX Formula to calculate the amount of change in Sales from Last Year and This Year. “Change” Formula.
21. (19:04) “% Change” DAX Formula to calculate the % change between last year’s period and this year’s period. See DIVIDE DAX Function.
22. (20:14) Add New Data to Sales Table to see that formulas will adjust.
23. (21:41) Summary

View on YouTube

Power BI for Accountants: Complete Introduction to Power BI Desktop to Build Reports & Dashboards

Posted on by Annie D

Complete Introduction to Power BI Desktop. This video will teach you about how to use Power BI Desktop to clean, transform data and make a Dashboard to Monitor Sales. Take multiple Text files and import them into a single table which can be used for Reports and Dashboards and can be updated easily when new data arrives.
Download Files:
Zipped Folder file Text Files: http://ift.tt/2uczRVa
Excel File: http://ift.tt/2vEnQFN
Finished File: http://ift.tt/2uc5gXA
Entire page with all Excel Files for All Videos: http://ift.tt/1kSFWvs
Excel Accounting Seminar: 2017 WAATP Tacoma Accounting Conferences at La Quinta Inn, July 12
This is the third video in a series of three videos.
Topics in Video:
1. (00:10) Download Files
2. (00:24) Video Introduction and review of Topics covered in video
3. (03:13) Introduction to Power BI Desktop
4. (04:15) Overview of process of using Power BI Desktop to build our specific Reports and Dashboards
5. (05:09) Open and Save Power BI File
6. (06:28) Import Excel dProduct Lookup Tables
7. (08:05) Import and Append Multiple Text Files with Sales Data
8. (15:11) Clean Data
9. (16:42) Discussion of Relationships and why they are important
10. (17:58) Create Calendar Table using DAX CALENDAR Table Function
11. (21:27) Create Calculated Columns with DAX Functions: MONTH, FORMAT and YEAR
12. (21:54) Create Relationships and discuss more about Relationships
13. (26:25) Save As to save Power BI File
14. (26:54) Create Cross Tabulated Power BI Report
15. (28:11) Show Values As % of Column Total
16. (30:35) Create Gross Profit Dash Board
17. (31:29) DAX Measure for Gross Profit %
18. (34:24) Column and Bar Chart as Slicer for Gross Profit Dashboard
19. (36:13) Dashboard Interactivity
20. (38:00) Add New Data And Watch Dashboard Update
21. (39:53) Conclusion & Summary
Event Sponsored by:
Puget Sound/Tacoma Chapter WAATP
Location:
La Quinta Inn
1425 E 27th St,
Tacoma, WA 98421
Event Details:
Wednesday, July 12 at 8 AM

View on YouTube