Power BI Connection Types and Data refresh

I wanted to take a quick moment to outline the different connection types and data refresh. To understand how Power BI refreshes your datasets, reports and dashboards, you must aware of the following concepts:

Storage modes and dataset type

• Power BI refresh types
• Power BI Report Server
• Storage modes and dataset type

A Power BI dataset can operate in one of the following modes to access data from variety data sources

• Import mode
• DirectQuery mode
• LiveConnect mode
• Push mode

Datasets in Import mode

Power BI imports the data from the original data sources into the dataset.

Because Power BI caches the data, Import mode dataset sizes can be substantial. Refer to the following table for maximum dataset sizes per capacity. Stay well below the maximum dataset sizes to avoid refresh issues that might occur if your datasets require more than the maximum available resources during a refresh operation.

Datasets in DirectQuery/LiveConnect mode

Power BI does not import data over connections that operate in DirectQuery/LiveConnect mode. Instead, the dataset returns results from the underlying data source whenever a report or dashboard queries the dataset.

Although DirectQuery mode and LiveConnect mode are similar in that Power BI forwards the queries to the source, it is important to note that Power BI does not have to transform queries in LiveConnect mode. The queries go directly to the Analysis Services instance hosting the database without consuming resources on shared capacity or a Premium capacity.

Live Connection refers to connecting directly to a Model (SSAS Multidimensional or Tabular) in this regard, all your modelling work is done on those. With Direct Query - you are connecting to a database, and you can create calculated columns and measures in Power BI. So, Power BI serves as your model layer, while keeping your data in your databases.

The Scheduled cache refresh section of the Datasets tab is not available for datasets in import mode. These datasets don't require a separate tile refresh because Power BI refreshes the tiles automatically during each scheduled or on-demand data refresh.

Push datasets

Push datasets don't contain a formal definition of a data source, so they don't require you to perform a data refresh in Power BI. You refresh them by pushing your data into the dataset through an external service or process, such as Azure Stream Analytics. This is a common approach for real-time analytics with Power BI. Power BI still performs cache refreshes for any tiles used on top of a push dataset.

Power BI refresh types

A Power BI refresh operation can consist of multiple refresh types, including data refresh, OneDrive refresh, refresh of query caches, tile refresh, and refresh of report visuals.

Data refresh

For Power BI users, refreshing data typically means importing data from the original data sources into a dataset, either based on a refresh schedule or on-demand. Power BI limits datasets on shared capacity to 8 daily refreshes. If the dataset resides on a Premium capacity, you can schedule up to 48 refreshes per day in the dataset settings. It is also important to call out that the shared-capacity limitation for daily refreshes applies to both scheduled refreshes and API refreshes combined. Also note that datasets on a Premium capacity don't impose limitations for API refreshes.

Data refreshes must complete in less than 2 hours on shared capacity. If your datasets require longer refresh operations, consider moving the dataset onto a Premium capacity. On Premium, the maximum refresh duration is 5 hours.

OneDrive refresh

If you created your datasets and reports based on a Power BI Desktop file, Excel workbook, or comma separated value (.csv) file on OneDrive or SharePoint Online, Power BI performs another type of refresh, known as OneDrive refresh. 

Unlike a dataset refresh during which Power BI imports data from a data source into a dataset, OneDrive refresh synchronizes datasets and reports with their source files. By default, Power BI checks about every hour if a dataset connected to a file on OneDrive or SharePoint Online requires synchronization.

Refresh of query caches

If your dataset resides on a Premium capacity, you might be able to improve the performance of any associated reports and dashboards by enabling query caching, as in the following screenshot. Query caching instructs the Premium capacity to use its local caching service to maintain query results, avoiding having the underlying data source compute those results.

Tile refresh

Power BI maintains a cache for every tile visual on your dashboards and proactively updates the tile caches when data changes. In other words, tile refresh happens automatically following a data refresh.

By default, Power BI maintains a single cache for every tile, but if you use dynamic security to restrict data access based on user roles, as covered in the article row-level security (RLS) with Power BI, then Power BI must maintain a cache for every role and every tile. The number of tile caches multiplies by the number of roles.

Refresh of report visuals

This refresh process is less important because it is only relevant for live connections to Analysis Services. 

How to configure Power BI report scheduled refresh

To refresh data in your Power BI report, a scheduled refresh plan must be created. This is done within the Manage area of a Power BI report. This schedule is same as SSRS Report Schedule.

Cheers!
Uma

How to load SNOMED data into MS SQL Database

You can find many collections of scripts on the web, here I used from West Coast Informatics.

http://www.westcoastinformatics.com/resources.html

There is no any script for MSSQL Server, but available for MySQL, Postgres and Oracle. In this approach create a MySQL database and convert it to MSSQL database. Otherwise you need rewrite the script your own including Java code.

As a prerequisite Java and MySQL installation is required.

You can download MySQL from https://dev.mysql.com/downloads/installer/

For Java, first make sure Java is already installed or not. If java is correctly installed, you will get something like this.

If not installed, you can download from https://java.com/en/download/win10.jsp

Load into a MySQL database

Create a schema called snomed in MySQL database

Download SNOMED CT® MySQL Database Load Scripts (for International Edition January 31, 2020) from West Coast Informatics site or from other sources.

In this case loading International Edition, download above highlighted once.

Change the database connection parameters in the batch file populate_mysql_db.bat

Get Snomed latest data

Download Snomed latest data from https://www.health.govt.nz/nz-health-statistics/classification-and-terminology/new-zealand-snomed-ct-national-release-centre/snomed-ct-software-tools or from other sources.

In this case, SnomedCT_InternationalRF2_PRODUCTION_20200309T120000Z.zip is used. Once you unzip and copy all the above 6 data script related files into this folder. You will see something like this. 

Change the source file names as per the source data in mysql_tables.sql file mostly the date part are different. In this case, I changed 20200131 to 20200309.

 'Snapshot/Terminology/sct2_Concept_Snapshot_INT_20200131.txt' to 'Snapshot/Terminology/sct2_Concept_Snapshot_INT_ 20200309.txt'

Run populate_mysql_db.bat file, this will take quite a bit of time to complete. This bat file will generate all the tables with data and views.

Load Transitive Closure

A file containing the transitive closure of the SNOMED CT subtype hierarchy. The transitive closure file is not currently distributed but can be generated from the snapshot relationship file using a script file.

Download Transitive Closure Scripts from for MySQL  http://www.westcoastinformatics.com/resources.html

Once you unzip it you will see something like this

Copy all the above files to the previous folder.

Generate transitive closure file from the snapshot relationship file, the code is written in Java.

Change the input and output file parameter in transitive_closure.bat and run

At the end you will see transitive closure file is generated

Next you need to load data from this file to MySQL database, for that edit the parameters in populate_mysql_db_tc.bat

Also change the Transitive Closure file path in mysql_tc_table.sql 

Change the parameters in populate_mysql_db_tc.bat and run

Once the batch file is completed, you will see the new Transitive Closure table with relevant views. This process will take quite a bit of time.

Migrate MySQL database to MSSQL

create a new database called Snomed in MSSQL server

 

Download SSMA for MySQL and follow the steps as per this

https://datamigration.microsoft.com/scenario/mysql-to-sqlserver?step=1&tabnav=true

Create a new project in SSMA and connect to the Snomed database in MySQL and MSSQL server

 

 

Create the conversion report that will generate an HTML report with conversion statistics and error/warnings. This report will help to understand conversion issues and its cause.

In this case, I didn’t get any issues for schema conversion, if you find any errors you need to change the local schema and regenerate the report to check everything ok. 

Only change here is target schema to dbo instead of snomed. In MySQL Snomed database click on tables then Snomed database schema details come in the right side as shown below.

 

To publish the schema, select the database from MSSQL Server database “Database” node in the “SQL Server Metadata Explorer” and choose “Synchronize with Database” from right-click menu options. This action will publish the MySQL schema to the SQL Server instance.

Then migrate the data

The migrated data report will pop up at the end and data will be ready in MSSQL database.

Cheers!

Uma

How to hold or disable a SQL Agent Job until other SQL Agent Job to complete

There are instances that one job might cause issues with another job that runs parallel. In this kind of situation, it is better to hold or disabled the job until other jobs are completed.

Step 1: Disable the job

sp_update_job is used for changes the attributes of a job

@enabled - Specifies whether the job is enabled (1) or not enabled (0)

EXEC dbo.sp_update_job  

    @job_name = @job_name_1  

    @enabled = 0;

Step 2: Wait to finish already running job

WHILE EXISTS (

SELECT 1

FROM msdb.dbo.sysjobactivity

JOIN msdb.dbo.sysjobs

ON sysjobactivity.[job_id] = sysjobs.[job_id]

WHERE sysjobactivity.[session_id] = (  

SELECT TOP 1 [session_id] 

FROM msdb.dbo.syssessions 

ORDER BY [agent_start_date] DESC)

AND sysjobactivity.[start_execution_date] IS NOT NULL

AND sysjobactivity.[stop_execution_date] IS NULL

AND sysjobs.[name] = @job_name_2

)

BEGIN

    WAITFOR DELAY '00:00:15'

END

Step 3: Once the job is completed, you can re-enable the job

 EXEC dbo.sp_update_job  

 @job_name = @job_name_1  

 @enabled = 0;

Cheers!
Uma

How to download email attachment from Exchange Server

The EWS Managed API provides a simple and full-featured interface for developing and extending applications that use Exchange Web Services (EWS). Whether you are using Visual Studio or another code editor to develop your EWS Managed API application, you will need to make a reference to the EWS Managed API assembly. If you haven't installed the EWS Managed API already, be sure to download the API.

https://docs.microsoft.com/en-us/exchange/client-developer/exchange-web-services/how-to-reference-the-ews-managed-api-assembly

Add required parameters  as per your requirements

You can get the code from here

https://docs.microsoft.com/en-us/exchange/client-developer/exchange-web-services/how-to-get-attachments-by-using-ews-in-exchange

Thanks,
Uma