Thoughts from Data Platform MVP and Microsoft RD – Dr Greg Low
Recently, I did an interview with Amit Bansal and the team from Data Platform Geeks about some of the aspects involved in implementing Power BI in the Enterprise.
We touched at some length on security and a number of issues around data models.
You can find the interview here: http://www.dataplatformgeeks.com/geek-talk-sep-03-2020-dr-greg-low-enterprise-power-bi/
I saw the above message when working with one of my clients today. The error says Only users with Power BI Pro licenses can publish to this workspace. And that would make sense if they hadn't already purchases Power BI Pro licenses for the user.
I checked online, and there were a number of comments about people seeing this error. There were the usual suggestions of logging out and back into Power BI. There was even one who'd quoted a Microsoft support person who said that Pro licenses don't work for up to 24 hours after you purchase them. (That sounds dubious to me).
We checked that the license had been assigned to the user. I was concerned that perhaps the licenses were purchased but not actually assigned to the users. All was good with the license assignment.
But then we noticed something odd:
Note that the user still had their Free Power BI license assigned to them, and for some unknown (and not very sensible) reason, that was the one that the system was using when they logged in.
We removed the free license assignment and all was then good.
I hope that helps someone else.
Many years ago, I spent a lot of time in universities. I ended up finishing my studies at QUT in Brisbane
and I have a great and continuing fondness for that institution. Earlier on though, amongst other universities, I did quite a lot of study through Charles Sturt University
Over the years, I've maintained a continuous link with my friends at Charles Sturt University (CSU). Back when we used to run Code Camps for both developers and DBAs, CSU were only too pleased to jump in to help us. From the minute we arrived that first day in Wagga Wagga, I knew it was going to be good. Associate Profession Irfan Altas was an amazing help and remains a friend to this day. I'm always pleased to get to chat to him.
Irfan asked me to be the guest speaker at a CSU graduation a few years back. That was a great honour.
What many people might not realise is that I've also been helping as an industry supervisor for students in PhD or Doctor of IT programs.
And that's the reason for this post. I was so excited yesterday to hear that one of the students that I've been supervising (in this case together with Professor Oliver Burmeister) has completed all the requirements to be admitted to his Doctor of IT degree.
Congratulations to Dr Georg Thomas ! That's a major life achievement for you.
Power BI is amazing. And it's starting to appear all over the place. Many enterprises don't know what to make of it though. Some are scared that it'll be the "next Access" where stores of uncontrolled data end up all over the organization. Power BI's mantra of "5 minutes to Wow" is spot on. It's easy to be impressed. But enterprises are often struggling with "what comes next after that 5 minutes?"
We've been implementing many enterprise-level projects that use Power BI, and I'd love to spend time showing you what we do.
Amit Bansal and the DPS team in India are hosting a virtual summit later this year. There are three days of data-related conference presentations delivered 24 x 7. If you can't find something of real interest to you, and in your time zone, you aren't looking. And the price? At present it's $89 USD. Yes you read that right. And it includes a year of access to the recordings of all the normal conference sessions.
As part of their Data Platform Virtual Summit event, there are also pre-cons and post-cons. Again they are amazing value.
I'm presenting a pre-con over two half days. At the current discount, it's $129 USD and if you use the discount code GREG@DPS, it's even cheaper. I'd love to see many of you attend. Let's talk about Power BI in the enterprise.
Now, this isn't a session about how to make great visuals, etc. I'll leave that to someone else. But if you want to really get into what makes a good underlying data model, how to integrate properly with security, how to structure projects, how we use Azure Analysis Services and/or Power BI Premium and more, this should be the right session for you.
You can book here.
I was editing using the Power Query editor in an Analysis Services project, hosted in Visual Studio 2019. When I tried to use "Add column by example", I received the error shown above:
Unexpected Error
Could not load file or assembly 'Microsoft.DataIntegration.TransformDataByExample, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad34e35' or one of its dependencies. The system cannot find the file specified.
The problem is that the assembly had not been deployed with the Analysis Services Designer.
Fortunately, the same assembly is used by Power BI Desktop. Because I had that installed on the same machine, I was able to copy the assembly:
Microsoft.DataIntegration.TransformDataByExample.dll
from the folder:
C:\Program Files\Microsoft Power BI Desktop\bin
to the same folder as the devenv.exe program from VS 2019:
C:\Program Files (x86)\Microsoft Visual Studio\2019\Enterprise\Common7\IDE\
If that path doesn't exist on your machine, it might be here instead if you're using the Community edition:
C:\Program Files (x86)\Microsoft Visual Studio\2019\Community\Common7\IDE\
After that, it was back working again. I hope that helps someone else.
In some upcoming posts, I'll discuss how you can change between data types, but I wanted to show you first, how you can use the FORMAT() function to convert dates and times to strings.
This query asks for the current date and time value formatted as day then month, then (four digit) year. It also includes the desired culture (en-US) which would be used if the format required any culture-specific items, such as the names of months.
You might wonder where that format string ddMMyyyy comes from. That's actually a .NET standard format, and so this is actually a wrapper function that calls the .NET Framework under the covers.
For this reason, it's also not a very fast function. If you can use CAST and/or CONVERT to do the formatting, always use that instead.
It's worth your while becoming proficient in SQL. If you'd like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.
I mentioned in the last T-SQL post how you could create a datetimeoffset value by combining a datetime2 with an offset. But sometimes you need to switch to a different offset. And that's what the SWITCHOFFSET() function does:
In this query, I was taking a local time (that had a timezone offset of 11 hours), and switching it to the current time in Seattle (with a -7 hours offset):
So this function allows us to take a datetimeoffset value and convert it to a different offset.
Keep in mind that even though we have handling of timezone offsets, SQL Server isn't timezone aware to any great deal. However, there is a function sys.time_zone_info that will tell you the current offsets and daylight savings settings for different timezones.
What it doesn't do is provide you with future or historical timezone info.
It's worth your while becoming proficient in SQL. If you'd like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.
It's great to have SQL Server data types now that handle time zone offsets. Sometimes though, you need to combine a datetime value and an offset to produce one of these new values. That's what the TODATETIMEOFFSET() function does:
In this example, I've taken 28th February 2019 and added a timezone offset of 10 hours to it:
the output data type is datetimeoffset.
The data type of the first value is actually datetime2.
It's worth your while becoming proficient in SQL. If you'd like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.
One of the more popular things I've ever written is my (free) SQL Server Management Studio Tips and Trips eBook.
I've had a few people asking why I haven't done a video on it lately. I'm planning to do a series on it very soon.
In the meantime, here's a session that I did with Amit and the people from Data Platform Geeks a while back: Tips and Tricks Session
It covers a number of tips and tricks, but if you want all of them, you'll find the eBook here: https://ssmsbook.sqldownunder.com/
I mentioned in previous T-SQL posts that one of the challenges is that there's no standard way to write dates, so we end up having to write them as strings. Now that was a real problem in earlier versions where people would get that wrong. The DATEFROMPARTS() function added in SQL Server 2012, though, can help with this:
This query takes the year 2019, the month 2, and the day 28 and returns a date:
All of the date related data types have options with FROMPARTS. So for example there's a DATETIMEFROMPARTS(), a DATETIMEOFFSETFROMPARTS(), etc.
Sadly, though, at the time of writing, the Intellisense for these in SSMS is very poor. Currently it doesn't tell you what each parameter is. That's not good.
It's worth your while becoming proficient in SQL. If you'd like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.
