wk3 aug_epic story_13: Produce DataUI SQL script

[dkngenda]

• [x] Extract SQL code from VBA code.
• [x] Indent the code to improve readablity of the code

[lzim]

@dkngenda @jamesmrollins

Did you find these too per our 8AM Workgroup Leads today?

VBA Glossary https://docs.microsoft.com/en-us/office/vba/language/glossary/glossary-vba

Documentation for all VBA Functions https://docs.microsoft.com/en-us/office/vba/Language/Reference/functions-visual-basic-for-applications

All VBA Functions for DAX and MDX https://docs.microsoft.com/en-us/sql/mdx/vba-functions-in-mdx-and-dax?view=sql-server-ver15

Excel to Dax https://community.powerbi.com/t5/Community-Blog/Excel-to-DAX-Translation/ba-p/1060991

Multidimensional Models in Power BI - Members, Columns, Filters and Slicers - Multidimensional analysisproduces the ability to analyze large quantities of data by storing data in axes and cells instead of the traditional relational two-dimensional view in rows and columns. https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-default-member-multidimensional-models

MDX Queries for members https://docs.microsoft.com/en-us/analysis-services/multidimensional-models/mdx/mdx-query-the-basic-query?view=asallproducts-allversions

Filters https://docs.microsoft.com/en-us/power-bi/create-reports/power-bi-report-filter-types

Slicers https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-slicers

Really helpful overview of DirectQuery vs. Import vs. Live Connection with a wide variety of considerations https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-directquery-about

Other things I learned - What did you learn?

Role of Power Query rather than SQL in Power BI Power Query has its own language for connections, queries and transformations, including connecting to a SQL database.

• The query, refining, and manipulation are not written in SQL, you build queries in Power BI's query editor.
• Power Query is for query mashups (e.g., SQL table, Excel worksheet, then running part of the result in our R Notebook). So, we may make our lives more difficult by trying to write (re-write) our own queries.

Power Query does query folding, creating that internally generated SQL statements, and writing our own often results in a less efficient queries. When you connect to a SQL Server source in Power BI's query editor, & pick a database, you can pick a table and edit the query or you can embed SQL query into a source statement in a Power BI query, but if you simply load the table Power BI will send a SELECT * FROM tablename without us writing it.

[dkngenda]

@lzim @jamesmrollins @staceypark

Things I've learned

1. While we can use power query to interact with CDW directly, complex queries that include temp tables will throw errors.

Suggested solution: -Building most of the data sets in SQL server then direct connect to them - I like this as it reduces any errors that might be introduced with any new DAX coding. This would also allow us to easily run things by Andrew (my assumption )

https://community.powerbi.com/t5/Desktop/Temp-Tables-in-Power-BI-with-Direct-Query/td-p/1039965

2. You can use R to perform data transformations (joins, summaries, graphics) by simply copying R scripts into power BI.

Illustration of this can be found here https://www.youtube.com/watch?v=ZHmmnNCd92w

3. There are external tools that can analyze your power BI model to identify points of optimization. I found this BISL resource quite useful along these lines.

https://web.microsoftstream.com/video/11d3f1f1-8449-4c10-a46d-1e1f9e364836?channelId=60c4e780-6c36-4f17-a461-cd6522d7e06f

4. After reviewing VBA code, I think that most of the VBA code does the following.

a) Automate reading values from the lookup tab in the dataUI file. b) Establish connection to servers. c) Create and populate filters that interact with data processed by SQL script. d) Create graphics. e) Call SQL scripts that do various data transformation/ calculations or create new SQL tables. f) Cache clinic selection during team setup.

Based on this code review , I think that.

• For a), we will probably need to create SQL tables to hold the lookup data and utilize those tables as needed in the other sections of the SQL script
• For b- d, Power BI has built in tools/ capacity to do these and I feel that we do not need translate VBA code to DAX code we can use what Power BI already provides
• Not sure how f) can be implemented in power BI.

@jamesmrollins could you please take a look at the dataUI file VBA CODE and help verify my thinking on 4 The dataUI file can be found at G:\Shared Research\TeamPSD\quant_workgroup\final_datafiles\DataToolFRE23_nevermore_online - David_copy

[jamesmrollins]

Hi @dkngenda

1. I acknowledge your request to review the VB code and render an opinion.
2. Understand the need to cache user session with selections - Priority Information Requirement we should ask at Pwer BI Office Hours tomorrow at 1200 PST.
3. Would like to also determine how we can auto-setup row level permissions using a CRM export. Priority Information Requirement we should ask PowerBI Office Hours tomorrow at 1200 PST.