Power BI on-prem gateway troubleshoot

Configuring proxy settings for the On-premises Data Gateway

Change the On-Premises Data Gateway service account

1. Change the Windows service account for the On-premises Data Gateway service.
   The default account for this service is NT SERVICE\PBIEgwService. You will want to change this to a domain user account within your Active Directory domain. Or, you will want to use a managed service account to avoid having to change the password.
   You will want to change the account on the Log On tab within the properties of the Windows service.
2. Restart the On-premises Data Gateway service.
   From an admin command prompt, issue the following commands.
   

   net stop PBIEgwService
   
   net start PBIEgwService
   

3. Start the On-premises Data Gateway configurator. You can select the windows start button and search for On-premises Data Gateway.
4. Sign in to Power BI.
5. Restore the gateway using your recovery key.
   This will allow the new service account to be able to decrypt stored credentials for data sources.

SQL 2017on linux

Part 1

Create vm on Azure using below template.
Red Hat Enterprise Linux 7.3

Connect to linux vm ( install bitvise ssh client on local and connect to VM)

---- install
sudo su
curl https://packages.microsoft.com/config/rhel/7/mssql-server.repo > /etc/yum.repos.d/mssql-server.repo
exit

sudo yum install -y mssql-server

sudo /opt/mssql/bin/mssql-conf setup

systemctl status mssql-server

sudo firewall-cmd --zone=public --add-port=1433/tcp --permanent
sudo firewall-cmd --reload


Part 2

-------------- backup and restore
you can restore the backup taken on windows server to linux as it is.

take backup of windows backup and copy to /var/tmp/backup

sudo su
chown mssql:mssql AdventureWorksDW2016CTP3.bak
--show logical name infor
restore filelistonly from Disk='/var/tmp/backup/AdventureWorksDW2016CTP3.bak'


-- Restore database backup

restore DATABASE AdventureWorksDW2016Linux from Disk='/var/tmp/backup/AdventureWorksDW2016CTP3.bak' WITH FILE = 1,
MOVE 'AdventureWorksDW2014_Data' TO '/var/opt/mssql/data/AdventureWorksDW2016CTP3.mdf',
MOVE 'AdventureWorksDW2014_Log' TO '/var/opt/mssql/data/AdventureWorksDW2016CTP3_log.ldf',
NOUNLOAD,
STATS = 5
GO

Power BI Premium

Power BI Premium

Previously available were two tiers, Power BI Free and Power BI Pro ($10/user/month).  The problem with Power BI Pro is that for large organizations, this can add up.  In addition, their performance needs might not be met.  Power BI Premium, which is an add-on to Power BI Pro, addresses the concern about cost and scale.

For costs, it allows an unlimited number of users since it is priced by aggregate capacity (see Power BI Premium calculator).  Users who need to create content in Power BI will still require a $10/month Power BI Pro seat, but there is no per-seat charge for consumption.

For scale, it runs on dedicated hardware giving capacity exclusively allocated to an organization for increased performance (no noisy neighbors).  Organizations can choose to apply their dedicated capacity broadly, or allocate it to assigned workspaces based on the number of users, workload needs or other factors—and scale up or down as requirements change.

There will be changes to the Power BI’s free tier.  Users of the free tier will now be able to connect to all of the data sources that Pro users can connect to, including those available through the on-premises data gateway, and their storage quota will increase from 1GB to 10GB.

 The data refresh maximum increases from once daily to 8 per day (hourly-based schedule), and streaming data rates increase from ten thousand rows per hour to one million rows per hour.

For Power BI Premium, you get 100TB of storage, data refresh maximum of 48 per day (minute-based schedule), and soon-to-be-available is that the dataset size cached limit is removed (it is 1GB in Power BI Pro), so you will be able to build models as large as the Power BI Premium dedicated capacity memory can hold (currently 50GB).

Upcoming features for Power BI Premium include the ability to incrementally refresh the data so that only the newest data from the last day (or hour) is loaded into Power BI, pinning datasets to memory, dedicated data refresh nodes, read-only replicas, and geographic distribution (see Microsoft Power BI Premium Whitepaper for more info).

Users of free tier will no longer be able to share their reports and dashboards with other users.  Peer-to-peer dashboard sharing, group workspaces (now called app workspaces), export to PowerPoint, export to CSV/Excel, and analyze in Excel with Power BI apps are capabilities limited to Power BI Pro.  The rationale for this is that if the scope of a user’s needs are limited to personal use, then no fees should apply, but if the user wishes to share or collaborate with others, those are capabilities that need to be paid for.  For existing users of the free service who have been active within the past year, Microsoft is offering a free, 12-month extended trial of Power BI Pro

If you are sharing dashboards/reports with free users, beginning June 1st they will need to take advantage of the extended Pro trial to continue accessing the content. After the extended trial expires, users will need a Pro license to maintain access.

Performance tuning tips

1) Format disk with 64K

2) Put tempdb separate disk and add multiple tempdb files ( 2016 on wards)

3) Data Warehouse use Columstore index,this would give performance improvement and grate compression rate

4) OLTP use In-Memory

5) SQL Server defaults to a maximum degree of parallelism (MAXDOP) of 0, which dictates that SQL will dynamically allocate work up to the total number of CPU cores seen by the SQL service. With Hyper Thread turned on for a system with 80 physical cores, SQL will see a total of 160 cores which can, for many workload mixes, lead to a sub-optimal allocation of SQL threads. To address this, we can change the “Max Degree of Parallelism” parameter in SQL Server Advanced properties to less than or equal to the number of physical CPU cores as shown in following figure.

6) In order to reserve some memory for system processes, user processes, programs, etc., we can change the SQL Server “Maximum server memory” setting in SQL Server properties. Typically, a value of total system memory minus 8GB is sufficient. This means that SQL Server will allocate all available memory in the system, except for 8GB

7) every table should have Clustered index ( row or Columstore )

Shrink Tlog

use below script in SP and schedule it maintenance window.

checkpoint;

go

DBCC SHRINKFILE('log file name',25)

The following query will give you the reason on why log file is not getting reused.



select name,log_reuse_wait_desc from sys.databases


If you get nothing as description for the logfile you will also be able to shrink the log file

SQL auto stats on or off

SELECT name AS 'Name', 

    is_auto_create_stats_on AS "Auto Create Stats",

    is_auto_update_stats_on AS "Auto Update Stats",

    is_read_only AS "Read Only" 

FROM sys.databases

WHERE database_ID > 4;

ALTER DATABASE YourDBName SET AUTO_CREATE_STATISTICS ON

get adhoc sql load in sql server

select objtype,p.size_in_bytes,sql.text
 from
sys.dm_exec_cached_plans p
outer apply sys.dm_exec_sql_text( p.plan_handle) sql