2Touch Power BI guide
2TouchPOS Power BI Guide
2016 Xenios, LLC 82 Saint Paul Street Rochester, NY 14604 Phone 585.325.5242 • Fax 585.325.6989
Table of Contents About this Document .................................................................................................................. 2
Installation .................................................................................................. 3 Initial setup ................................................................................................. 4 Power BI Desktop ........................................................................................................................ 4 Power BI Website........................................................................................................................ 8
Best practices and recommendations ....................................................... 10 Datasets .................................................................................................................................... 10 Reports ...................................................................................................................................... 10 Queries, sorting and filtering .................................................................................................... 10 Columns and Measures............................................................................................................. 10
Appendix: Advanced Dataset configuration.............................................. 12 Power BI Desktop ...................................................................................................................... 12
About this Document This document describes the steps needed to install and configure Power BI for use with the 2Touch DataKey OData API. Power BI Desktop is required and Power BI website is recommended. Please see section Best practices and recommendations for more details. Please see the Power BI documentation for details on using Power BI.
Xenios, LLC @2016
Page 2
Installation 1. Go to https://powerbi.microsoft.com/en-us/ 2. Under Products, select Power BI Desktop 3. Download and install Power BI Desktop
Xenios, LLC @2016
Page 3
Initial setup Power BI Desktop 1. It is strongly recommended that you close all applications prior to running these steps. 2. Download and save the following file https://datakey.2tpos.com/2TouchPOS/DataKey/V4/OData/2TouchTicketTemplate.pbit or https://datakey.2tpos.com/2TouchPOS/DataKey/V4/OData/2TouchTicketTemplateWithReports.pbit 3. Using the main menu Import the Power BI Template file saved in step 2.
4. When prompted for credentials select Basic, enter 00000000-0000-0000-0000-000000000000 for the username and password and change apply these settings to the URL (matching the url in the
image below)
Xenios, LLC @2016
Page 4
5. Wait for the refresh to complete Note: Wait for the refresh to complete and the window to close. 6. Using the main menu Open the Data source settings
7. Select the data source and select change source
Xenios, LLC @2016
Page 5
8. Replace the highlighted value 00000000-0000-0000-0000-000000000000 with your Site ID
9. 10. Select OK and Select Edit Permissions 11. Select Edit
Xenios, LLC @2016
Page 6
12. Select Basic; Enter b3047071-95bc-4450-ada9-4ca000083b66 for the username and your auth code for the password.
13. Save 14. Close 15. Apply Changes
Note: This step will be importing your data and will take a while depending on the amount of information. 16. If you plan on using Power BI Website a. Login b. Click Publish (save the file if prompted) Note: This step will be uploading your data to the Power BI cloud and will take a while depending on the amount of information. c. If the publish fails you may need to upload it via the Power BI Website section
Xenios, LLC @2016
Page 7
Power BI Website 1. https://powerbi.microsoft.com/en-us/ 2. Sign In (Create an account if needed) 3. This step can be skipped if you successfully published from Power BI Desktop a. Select Get Data
b. Select Get under files
c. Select Local File d. Browse to the file saved from Power BI Desktop 4. Using the Dataset Menu, select SCHEDULE REFRESH
Xenios, LLC @2016
Page 8
5. Expand Data source credentials and hit Edit credentials 6. Change Authentication method to Basic, enter b3047071-95bc-4450-ada9-4ca000083b66 for the username and your auth code for the password
7. Scheduled Refresh (Optional) a. Select Weekly refresh b. Select the day(s) and time to refresh c. Apply
Xenios, LLC @2016
Page 9
Best practices and recommendations Datasets It is recommended that you create only one dataset per site and use filtering within the reports. Having only one copy of the data will limit the amount of time needed to refresh and the amount of storage used. The Power BI website allows for scheduling the automatic refreshing data. It also allows you to view reports via any web browser (does not require Power BI Desktop to be installed) or mobile phone app. Power BI Desktop requires manual refreshes and can only be viewed in Power BI Desktop on the computer that generated the dataset/report. Some fields are hidden by default. You can right click on the fields area and select “view hidden” to see these fields.
Reports Reports can be created in either Power BI Desktop or the Power BI Website. The website can be used to create simple reports that do not need additional fields or filtering and does not require additional publishing time. The desktop version allows users to create additional fields and other advanced features.
Queries, sorting and filtering The 2Touch DataKey OData API does not support these features at API level so editing queries via the Query Editor will not be applied to the dataset. When possible create page or report level filters. This can be done either by dragging and dropping fields in the filters boxes or by adding a Slicer visualization to the page. If these solutions do not work with the available fields try creating a Columns and Measures to create field to filter on.
Columns and Measures Columns and measures are useful for creating calculated values. A column should be used when creating a new value that is based on other values that won’t change after the column is created. A measure is needed when one of the values in the equation might change over time such as using date and time functions like TODAY() or NOW(). Measures get re evaluated for each run of a report. See Power BI and DAX syntax for more information. Example Column which returns the Hour that a ticket was created on. CreatedOnHour = HOUR(Tickets[CreatedOn]) Example Measure which returns true if the ticket was created this week. Xenios, LLC @2016
Page 10
CreatedOnThisWeek = If((YEAR(Tickets[CreatedOn]) = YEAR(TODAY()) && ((WEEKNUM(Tickets[CreatedOn]) = WEEKNUM(TODAY())))), TRUE(), FALSE())
Xenios, LLC @2016
Page 11
Appendix: Advanced Dataset configuration Note: This section is for advanced Power BI users and not required when importing the 2Touch Power BI Templates.
Power BI Desktop 1. Open Options
2. Turn off the following options on the Data Load tab for the best performance a. Import relationships from data sources b. Update relationships when refreshing queries c. Autodetect new relationships d. Allow data preview to download in the background e. Enable parallel loading of tables
Xenios, LLC @2016
Page 12
3. Select Get Data OData Feed
Xenios, LLC @2016
Page 13
4. Enter the URL https://datakey.2tpos.com/2TouchPOS/DataKey/V4/OData/{00000000-0000-0000-0000000000000000} replacing the zeros with the site id
5. 6. Select Basic and enter b3047071-95bc-4450-ada9-4ca000083b66 for the username and your auth code for the password and apply these settings to the URL entered in the step above.
7. Select the down arrow next to Display Options and unselect Enable data previews
8. Check the data items needed for this dataset and click load
Xenios, LLC @2016
Page 14
9. Wait for the Load to complete Note: This step will be importing your data and will take a while depending on the amount of information. 10. Publish (save the file if prompted) Note: This step will be uploading your data to the Power BI cloud and will take a while depending on the amount of information. 11. If the publish fails you may need to upload it via the Power BI website (See above section)
Xenios, LLC @2016
Page 15
2016 Xenios, LLC 82 Saint Paul Street Rochester, NY 14604 Phone 585.325.5242 • Fax 585.325.6989
Table of Contents About this Document .................................................................................................................. 2
Installation .................................................................................................. 3 Initial setup ................................................................................................. 4 Power BI Desktop ........................................................................................................................ 4 Power BI Website........................................................................................................................ 8
Best practices and recommendations ....................................................... 10 Datasets .................................................................................................................................... 10 Reports ...................................................................................................................................... 10 Queries, sorting and filtering .................................................................................................... 10 Columns and Measures............................................................................................................. 10
Appendix: Advanced Dataset configuration.............................................. 12 Power BI Desktop ...................................................................................................................... 12
About this Document This document describes the steps needed to install and configure Power BI for use with the 2Touch DataKey OData API. Power BI Desktop is required and Power BI website is recommended. Please see section Best practices and recommendations for more details. Please see the Power BI documentation for details on using Power BI.
Xenios, LLC @2016
Page 2
Installation 1. Go to https://powerbi.microsoft.com/en-us/ 2. Under Products, select Power BI Desktop 3. Download and install Power BI Desktop
Xenios, LLC @2016
Page 3
Initial setup Power BI Desktop 1. It is strongly recommended that you close all applications prior to running these steps. 2. Download and save the following file https://datakey.2tpos.com/2TouchPOS/DataKey/V4/OData/2TouchTicketTemplate.pbit or https://datakey.2tpos.com/2TouchPOS/DataKey/V4/OData/2TouchTicketTemplateWithReports.pbit 3. Using the main menu Import the Power BI Template file saved in step 2.
4. When prompted for credentials select Basic, enter 00000000-0000-0000-0000-000000000000 for the username and password and change apply these settings to the URL (matching the url in the
image below)
Xenios, LLC @2016
Page 4
5. Wait for the refresh to complete Note: Wait for the refresh to complete and the window to close. 6. Using the main menu Open the Data source settings
7. Select the data source and select change source
Xenios, LLC @2016
Page 5
8. Replace the highlighted value 00000000-0000-0000-0000-000000000000 with your Site ID
9. 10. Select OK and Select Edit Permissions 11. Select Edit
Xenios, LLC @2016
Page 6
12. Select Basic; Enter b3047071-95bc-4450-ada9-4ca000083b66 for the username and your auth code for the password.
13. Save 14. Close 15. Apply Changes
Note: This step will be importing your data and will take a while depending on the amount of information. 16. If you plan on using Power BI Website a. Login b. Click Publish (save the file if prompted) Note: This step will be uploading your data to the Power BI cloud and will take a while depending on the amount of information. c. If the publish fails you may need to upload it via the Power BI Website section
Xenios, LLC @2016
Page 7
Power BI Website 1. https://powerbi.microsoft.com/en-us/ 2. Sign In (Create an account if needed) 3. This step can be skipped if you successfully published from Power BI Desktop a. Select Get Data
b. Select Get under files
c. Select Local File d. Browse to the file saved from Power BI Desktop 4. Using the Dataset Menu, select SCHEDULE REFRESH
Xenios, LLC @2016
Page 8
5. Expand Data source credentials and hit Edit credentials 6. Change Authentication method to Basic, enter b3047071-95bc-4450-ada9-4ca000083b66 for the username and your auth code for the password
7. Scheduled Refresh (Optional) a. Select Weekly refresh b. Select the day(s) and time to refresh c. Apply
Xenios, LLC @2016
Page 9
Best practices and recommendations Datasets It is recommended that you create only one dataset per site and use filtering within the reports. Having only one copy of the data will limit the amount of time needed to refresh and the amount of storage used. The Power BI website allows for scheduling the automatic refreshing data. It also allows you to view reports via any web browser (does not require Power BI Desktop to be installed) or mobile phone app. Power BI Desktop requires manual refreshes and can only be viewed in Power BI Desktop on the computer that generated the dataset/report. Some fields are hidden by default. You can right click on the fields area and select “view hidden” to see these fields.
Reports Reports can be created in either Power BI Desktop or the Power BI Website. The website can be used to create simple reports that do not need additional fields or filtering and does not require additional publishing time. The desktop version allows users to create additional fields and other advanced features.
Queries, sorting and filtering The 2Touch DataKey OData API does not support these features at API level so editing queries via the Query Editor will not be applied to the dataset. When possible create page or report level filters. This can be done either by dragging and dropping fields in the filters boxes or by adding a Slicer visualization to the page. If these solutions do not work with the available fields try creating a Columns and Measures to create field to filter on.
Columns and Measures Columns and measures are useful for creating calculated values. A column should be used when creating a new value that is based on other values that won’t change after the column is created. A measure is needed when one of the values in the equation might change over time such as using date and time functions like TODAY() or NOW(). Measures get re evaluated for each run of a report. See Power BI and DAX syntax for more information. Example Column which returns the Hour that a ticket was created on. CreatedOnHour = HOUR(Tickets[CreatedOn]) Example Measure which returns true if the ticket was created this week. Xenios, LLC @2016
Page 10
CreatedOnThisWeek = If((YEAR(Tickets[CreatedOn]) = YEAR(TODAY()) && ((WEEKNUM(Tickets[CreatedOn]) = WEEKNUM(TODAY())))), TRUE(), FALSE())
Xenios, LLC @2016
Page 11
Appendix: Advanced Dataset configuration Note: This section is for advanced Power BI users and not required when importing the 2Touch Power BI Templates.
Power BI Desktop 1. Open Options
2. Turn off the following options on the Data Load tab for the best performance a. Import relationships from data sources b. Update relationships when refreshing queries c. Autodetect new relationships d. Allow data preview to download in the background e. Enable parallel loading of tables
Xenios, LLC @2016
Page 12
3. Select Get Data OData Feed
Xenios, LLC @2016
Page 13
4. Enter the URL https://datakey.2tpos.com/2TouchPOS/DataKey/V4/OData/{00000000-0000-0000-0000000000000000} replacing the zeros with the site id
5. 6. Select Basic and enter b3047071-95bc-4450-ada9-4ca000083b66 for the username and your auth code for the password and apply these settings to the URL entered in the step above.
7. Select the down arrow next to Display Options and unselect Enable data previews
8. Check the data items needed for this dataset and click load
Xenios, LLC @2016
Page 14
9. Wait for the Load to complete Note: This step will be importing your data and will take a while depending on the amount of information. 10. Publish (save the file if prompted) Note: This step will be uploading your data to the Power BI cloud and will take a while depending on the amount of information. 11. If the publish fails you may need to upload it via the Power BI website (See above section)
Xenios, LLC @2016
Page 15
