SQL Server Techniques: October 2019

Wednesday, October 30, 2019

Power BI Desktop: How to determine a Connectivity mode for a query? Can I change it?

After you build Power BI dashboard you want to make sure that everything is set as it supposed to be.

Or, you are troubleshooting PBI dashboard performance and trying to figure out what it is doing.

For a Power BI dashboard it is very important if it runs against live data or internally imported data.
Which Connectivity/Storage mode your query is using, "DirectQuery" or "Import"?

That is very easy to determine, just put your cursor above name of your data set, and after about one second an informational label will tell you a Storage mode for that query.
Looks like this:

As you can in my dashboard: data set "Objects" is "Imported", but data set "Partitions" has "DirectQuery" as a source.

Now will take a look on how to change it.
So far I haven't found a menu option for doing this, but it is still possible:

1. First, start "Query editor". Then:
- Highlight your query (Partitions);
- Choose "Transform" tab in a menu;
- Click on "Group By" menu item.

2. You do not have to do anything in the "Group By" interface. Just hit "OK".

3.Than "Switch all tables to Import mode" button should appear at the top of your data:

4. Remember that is the ONE WAY ROAD, you won't be able to get back if you press this button. If you just will try to apply the changes without switching you'll get an error message like this:

5. Whether you decided to switch from "DirectQuery" to "Import" Storage mode you have to clean your "Grouping By" change by deleting it. Just simply click on a cross on the left side of the pane of your query:

At the end, I want to point again:
You can easily switch from "DirectQuery" to "Import" Storage mode, but you can't go back.
So, be careful with it.

Wednesday, October 23, 2019

Integer Number limitation in Power BI

By doing heavy calculations with big integer numbers I've hit Power BI whole number limit, which is "9,007,199,254,740,992".

If you try to get to any number higher than that Power BI will start rounding your results.

Here is what I've found about that problem online:

Source: Data types in Power BI Desktop

I've done my own research and here is what I've found.
Here are two queries I've created for the test:

SELECT TOP 62 [Power]=ROW_NUMBER() OVER(ORDER BY (SELECT NULL))

,[Number]=POWER(CAST(2 AS BIGINT),ROW_NUMBER() OVER(ORDER BY (SELECT NULL)))

,[Number+1]=POWER(CAST(2 AS BIGINT),ROW_NUMBER() OVER(ORDER BY (SELECT NULL)))+1

,[Number-1]=POWER(CAST(2 AS BIGINT),ROW_NUMBER() OVER(ORDER BY (SELECT NULL)))-1

FROM sys.messages;

SELECT TOP 62 [Power]=ROW_NUMBER() OVER( ORDER BY ( SELECT NULL))

,[Number]=-POWER(CAST(2 AS BIGINT),ROW_NUMBER() OVER(ORDER BY (SELECT NULL)))

,[Number+1]=1-POWER(CAST(2 AS BIGINT),ROW_NUMBER() OVER(ORDER BY (SELECT NULL)))

,[Number-1]=-1-POWER(CAST(2 AS BIGINT),ROW_NUMBER() OVER(ORDER BY (SELECT NULL)))

FROM sys.messages;

First query produces positive powers of "2" and second produces negatives.
The result in SSMS looks like this:

I've used both queries in Power BI Desktop and here is what I've got:

The result was expected, all numbers bigger than "9,007,199,254,740,992" and less than "-9,007,199,254,740,992" were rounded, but I've got something totally unexpected.

Look at the highlighted red boxes. The SUM of positive numbers produces negative Total and the SUM of negative numbers produces positive Total!

That might be a "feature", but it looks like a bug for me.

That behavior persists in the newest release: Version: 2.74.5619.841 64-bit (October, 2019)

Tuesday, October 22, 2019

Link tables in Power BI by two columns

Power BI is not always friendly to BI developer and you need to do some work around to get what you need.
Here is a simple case:

The Problem:

We have two tables in Power BI Desktop, which we need to link, but it happens they have to be linked by two columns.

The Case:

Simplest case is to create two Queries to a "master" table in any SQL Server. Call them "Partitions" and "Indexes":

Here are these simplest queries:

-- Partitions

SELECT * FROM sys.dm_db_partition_stats;

-- Indexes

SELECT * FROM sys.indexes;

If we go now to a model tab and try to link tables by "object_id" column . . .

We will get a message that we can build only "Many-2-Many" relationship and if we want to do a "One-2-Many" we need to have a column to be unique on one side:

The problem is that we can't select two columns to link those two tables to establish relationship.

The Solution:

Do a right click on "Indexes" query in the "Fields" tab and select "New Column":

Specify following formula in the "New Column" box at the upper left:

Index_Key = POWER (2,32) * Indexes[index_id] + Indexes[object_id]

Like this:

Create new column in the "Partitions" the same way as for "Indexes":

The formula for Partitions will be only slightly different:

Index_Key = POWER (2,32) * Partitions[index_id] + Partitions[object_id]

Will look like this:

As the result, you will see the New columns in the modeling view, where you can click on "Manage Relationship" button in the menu.

For the new relationship specify following:
1. First table: "Indexes"
2. Second table: "Partitions"
3. Select the very last column "Index_Key" in both tables
4. Specify cardinality as "One to many (1:*)"
5. Apply security filter in both directions (optional)
6. Hit OK

Now you are supposed to get such a beautiful "One-2-Many" relationship:

Possible caveats:

In my example "Index_id" numbers are very small.
If your calculated number hits a value bigger than "POWER (2,53)", which is the current Power BI Desktop application limit for the Whole numbers, in numeric form the limitation number is: "9007199254740992"
*That is applicable for version of: 2.73.5586.661 64-bit (September, 2019)

In that kind of situation you can use a string concatenation formulas like this:

Index_Key = Indexes[object_id] & "-" & Indexes[index_id]
Index_Key = Partitions[object_id] & "-" & Partitions[index_id]

*The "-" symbol is very important, because you can hit the situations when two numbers can possibly generate a similar string like "1&11" produces "111" and "11&1" also produces "111", which won't allow you to establish the uniqueness.

Thursday, October 17, 2019

When a database was last accessed?

You might say: "- What the silly question.... I'm accessing it right now!"
And you would be absolutely right if your database is online, but what if it is offline?

How to figure out when database got offline?

One way id to try to bring you database in question online and then look at the objects/indexes/statistics etc. and you might figure it out.
However there is much easier method without bringing database online.
Just use CMD or PowerShell.

At first, you need to figure out location of your database's log file:

SELECT physical_name

FROM master.sys.master_files

WHERE type = 1 and database_id = DB_ID('sysdb');

Then you can run a CMD commands like these to get Last Write and Last Access times:

dir N:\Logs\sysdb_log.ldf /TW
dir N:\Logs\sysdb_log.ldf /TA

Or you can use simple PowerShell command to get the same info, but with precision to the seconds.

ls N:\Logs\sysdb_log.ldf | Format-table -Property LastWriteTime, LastAccessTime

In my case result was horrible!

The database was sitting offline for more than a year and nobody paid attention to it.

Isn't t easy?

Thursday, October 10, 2019

Something went wrong with PowerBI report

Got a request from a customer earlier today about very strange error:

Something went wrong
Unable to load the data model for your Power BI report.
Please try again later or contact support. If you contact support, please provide these details.
An unknown error has occurred when attempting to load the report data. Please contact your system administrator.:
Request ID: 448f3cae-3a15-9a9a-f715-7b67812b8fd5
Status code: 500
Time: Thu Oct 10 2019 15:40:13 GMT-0400 (Eastern Daylight Time)
Version: 15.0.1102.299

After a little research it has been discovered that errored report does not hit a database at all.
Which led to an idea of wrong credentials.
After credentials were updated customers were able to successfully run the report.

Here are the easy steps:

Step #1. Right click on the report's three dots (...) and choose "Manage" from the list.

Step #2. Select "Data sources" in the report's left panel.

Step #3. Update user credentials.

I haven't had time to investigate the issue, but it looks like after report was deployed and tested, something went wrong and credential record from the newly loaded report stopped matching Server's credential record, which stopped report from running.

The idea is: to use windows authentication or re-establish credentials after every new deployment.

Tuesday, October 8, 2019

Absolutely unique timed file names

That is pretty common task to generate new files with a timestamp in their names.
It gives you ability to easily identify them, sort them and make them pretty unique.
However, if you have a very busy process it is possible that duplicate name will be produced and you might loose some data.

To avoid that situation I've came up with following solution.

Script #1. Generating timestamp.

The shortest query to generate only-numeric timestamp I've came up is that:

SELECT REPLACE(REPLACE(REPLACE(REPLACE(SYSDATETIME(),'-',''),' ',''),':',''),'.','');

It produces pretty unique output like this: "201910080935132176384"
But if you are not satisfied by that uniqueness you can add some more complexity by randomizing.

Script #2. Generating random number.

SELECT CAST(ABS(CAST(CAST(NewID() as BINARY(16)) as BIGINT)) as VARCHAR);

That generates a very unique numeric sequence up to 19 characters long like this: "4978829248728009422". If you'd like to combine it with time stamp it will be up to 40 characters long.

Script #3. Generating absolutely unique timed file name.

SELECT 'MyFile_'

+ REPLACE(REPLACE(REPLACE(REPLACE(SYSDATETIME(),'-',''),' ',''),':',''),'.','')

+ CAST(ABS(CAST(CAST(NewID() as BINARY(16)) as BIGINT)) as VARCHAR(40)) + '.txt';

That script will produce an absolutely unique, but precisely timed file name for you like this:
"MyFile_2019100809405568226626782079553600129740.txt"

If you wish, you have an ability to play around and insert underscores as dividers between date and time, seconds and milliseconds, time stamp and a random number. For instance you can do something like this: "MyFile_20191008_094601_4511373_6188309678908101054.txt".

SELECT 'MyFile_'

+ REPLACE(REPLACE(REPLACE(REPLACE(SYSDATETIME(),'-',''),' ','_'),':',''),'.','_')

+ '_' + CAST(ABS(CAST(CAST(NewID() as BINARY(16)) as BIGINT)) as VARCHAR(40))

+ '.txt';

Tuesday, October 1, 2019

PASS Summit 2019. Tips for the first-timers.

It is just one month before PASS Summit 2019.

This year I volunteered to help with "Buddy Program" for first timers. To help myself to help the first timers I've started making notes on the most important points of the event to share them with new attendees and newbies in #SQLFamily.
If you already been at PASS Summit, please read carefully and make a comment on what I've missed. If you already have links to sponsored learning events or parties, please let me know and I'll also post it here.
Thanks.

PASS Summit 2019 TIPS:

PASS Summit registration

Register as early as possible, it can dramatically reduce your cost. If you do it early enough, there can be extra savings for returning attendees;
Register for Pre-Con sessions if your budget allows. That is only $500 for the whole day session, but you are already paying for flight tickets and adjusting to the timing zone. So, it is not big money to learn from the World’s Best professionals. Also, do it as early as possible, some pre-con sessions were sold-out 2-3 months before the event.
Wisely choose food options.

Before you go

Visit “Activities” section: https://www.pass.org/summit/2019/Attend/Activities.aspx
Register for sponsored breakfasts – Eat breakfast, get some exclusive vendors’ information and possible obtain cool swags;
Register for different “Luncheons” – Eat, socialize, gain some knowledge;
Review PASS Summit schedule for sessions you’d like to attend. Read descriptions. Make an attendance plan;
Talk to your co-workers, ask them what they would like to know from those PASS Summit sessions, it might help you to decide which session to attend;
Also, collect any SQL Server related question from the team, write them down. You might have those questions answered or addressed by Microsoft’s CAT team at the event;
Load “PASS Events” application on your phone. Mark sessions you want to attend, it will remind you where you are supposed to go;
SQL community lives in Twitter. Download the app on your phone and start follow SQL gurus. Look for hash tags #SQLFamily #PASSsummit #sqlpass #SQLServer #SQLSaturday. Follow @sqlpass.
Review list of sponsors (https://www.pass.org/summit/2019/Sponsors/SponsorsExhibitors.aspx) Look for their twitter tags and also follow them. Some sponsors do cool parties during the event, give prizes and sometimes even give away free tickets to PASS Summit!!!
Book your flight and hotel. The best time to arrive is Sunday before the event, flights are cheaper then and you do not spend your work time for travel, you can attend Pre-Cons on Monday –Tuesday or attend other SQL related activities or just get familiar with Seattle and visit local attractions;
Before PASS Summit, you might decide to go to SQL Saturday in Portland, OR. Another free event in the area if you are flying from another side of the country or the Globe. You can adjust your timing there and take a #SQLTrain to Seattle on Sunday. #SQLTrain – are 2 carts of SQL professionals from around the world stack together for 4-5 hours eating famous Portland’s donuts and drinking beer and wine: https://www.sqlsaturday.com/920/eventhome.aspx;

Flight & Hotel

Arriving Sunday is the best. Returning flight is late Friday night or Saturday Morning, unless you want to stay longer, but from my experience better to come earlier. That time winter comes to Seattle and one week earlier is one week wormer;
Book hotel in 10-15-20 minutes walking distance from the convention center. It gives a flexibility to attend multiple early and late events not be tired and have just enough of sleep;

Coming to Seattle, WA

If you decided to go to SQL Saturday in Portland and get a #SQLTrain from there it will be easy for you to arrange with new friends an Uber to your hotel. You also can just walk to your hotel, the train station is almost in Seattle’s downtown area.
If you fly in Tacoma airport, the cheapest and very easy option would be to get a “Link Light rail” to the downtown area. It costs jus around $3-$4. Travel time might be the same as with Uber, but much cheaper. The same way you can get back, but be aware that on Sunday Light rail start operating only at about 6 A.M.

Monday before the PASS Summit

If you missed Pre-Con session look for Free-Con or SQL-in-the-City sponsored events on Monday-Tuesday before the PASS Summit. Do not miss it, it is free, you’ll learn a lot, meet new people and get cool swags;
If you are here for pleasure, look for the “Seattle’s Attractions” section of that list;
The PASS Summit Networking Dinner will be on Monday Nov. 4th, meeting at Tap House Grill (1506 6th Avenue, Seattle, WA) (5:30 pm - 8:30 pm). – Must go!

Tuesday before the PASS Summit

If you missed Pre-Con session and there is on Free-Con around you can do #sqlphotowalk (another thing to look in twitter). People who likes photography starts from convention center fountain area usually at 9 A.M. You can join them even if you do not have a camera, you’ll see Seattle’s deepest hidden secrets. However, this year, unfortunately for first timers it is scheduled for later time 4:45 pm - 6:00 pm. But check the twitter, there might be people who’ll walk at the morning;
Also, look for the “Seattle’s Attractions” section;
If you want to meet grass roots of SQL Community attend special sessions for SQL Saturday organizers, Local group leaders and virtual group leaders. Meet there your local SQL leader, join and support them, become a local leader yourself;
Register for the PASS Summit at the reception. You can do it as early as Sunday, but do not wait until Wednesday, when there will be long lines!!!
Attend “First-Timer Event” (4:45 PM - 6:00 PM) – Must go!
Welcome Reception (6:00 PM – 7:30 PM) – Must go!

Wednesday – First day of PASS Summit ("Community Appreciation Day")

"Community Appreciation Day" - wear your t-shirt, polo or a Hat with SQL Saturday logo;
Breakfast with a Sponsor;
Community zone – you can always hookup there with some cool people from #SQLFamily and meet some world famous speakers. Every time you pass by, stop there and say “Hi”;
Summit Keynote session;
Sponsors’ exhibitions – Learn new products, meet new people get cool swags. Some sponsors might distribute free tickets to different sponsored events and presentations. Do not miss it;
PASS Summit sessions;
Luncheon session;
If you have not submit for a luncheon, grab few friends or speakers and go to a nearest restaurant;
Do not miss “Speaker Idol” session. You might want to do it next year too!
Microsoft CAT team – attend that section of the Summit, they are supposed to know everything about SQL Server and Azure;
Exhibitor Reception (6:00 PM - 8:00 PM) – Must go!

Thursday – the Second day of PASS Summit ("Kilt Day")

"Kilt Day" - if you brave enough, wear a kilt or a silly costume;
Same as the previous day: Sponsor’s Breakfast, Keynote, sponsors’ boots, sessions, “Speaker Idol”, Etc.
If you did not get a free ticket to a sponsor’s event you can grab new friends and explore local restaurants. Do not neglect the opportunity to extend your network. You’ll see these people’s tweets and read their blogs since the event. You also might become a friends or/and do a business together.

Friday – the Last day of PASS Summit

Tips are the same as for previous days;
Attend the final round of “Speaker Idol” – that is where all cool people are!
Attend reception and order discounted USB drive with all session recordings for your co-workers and for you to watch missed sessions;
Possibly purchase PASS swags to have greatest memory from the Summit;
Get an agreement from speakers you met, to speak remotely for your local SQL user group about most interesting topic you like;
Get the last chance to have a dinner with other attendees or maybe go to any Seattle’s Attractions;
Say final “good bye” to new friends and contacts, exchange emails and twitter accounts;

After the Event

Review you PASS Summit notes and implement the best ideas at your workplace ASAP, before you forgot how cool they are!
Share your knowledge to your co-workers and local user groups;
Attend local SQL user group meetings and nearest SQL Saturday event. If do not have one – organize it!
Continue to follow your friends, speakers and #SQLFamily in twitter;
Register to the Next year’s PASS Summit in Huston, TX as soon as registration will be available to get the lowest price;

Seattle’s Attractions

Seattle’s Monorail – will get you right to a “Space Needle”, museum of Pop Culture and “Chihuly Garden and Glass”;
On the Bay side you can walk by peers, visit famous “Pike Market” and walk up to “Olympic Sculpture Park”;
Couple of historical tours: “Klondike Gold Rush National Park” and “Seattle Underground”;
For “Office Space” folks: you can visit offices of Starbucks, Amazon and Microsoft;
Easy getaway: Very inexpensive ferry to another side of the bay or take a sunset cruise;
Also, you can visit real nearby attractions and get tours to “Boing Factory”, “Olympic national park”, “Mt. Rainier” or “St. Helens Volcano”;

General tips (just repeating):

Learn SQL. Have a notepad or tablet to make quick notes at sessions; Having little notebook would allow you to immediately try newly announced features or show a live problem to the Microsoft CAT team;
Make more friends;
Get engaged in SQL community;
Follow friends, sponsors and organizers in twitter;
Attend events;
Have fun;
Continue to learn SQL!