Loading Data using R and PowerBI (2022-05-27)

SQLServerCentral Newsletter for May 27, 2022 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Loading Data using R and PowerBI]( - [Simple Reporting with Flyway and Database Models]( - [Memory profiling in Python with tracemalloc]( - [From the SQL Server Central Blogs - Data Engineer Diary: How to make Power BI Gateway work in an environment with a proxy]( - [From the SQL Server Central Blogs - KQL Series – Interactive Analytics with Azure Data Explorer]( Question of the Day - [Starting on Monday]( The Voice of the DBA  Daily Coping Tip Think about a journey you are on in life and your progress. I also have [a thread at SQLServerCentral]( dealing with coping mechanisms and resources. Feel free to participate. For many of you out there working in a new way, I'm including a thought of the day on how to cope in this challenging time from [The Action for Happiness Coping Calendar](. My items will be on [my blog](, feel free to share yours. The Learning Choice This editorial was originally published on Feb 8, 2018. It is being re-published as Steve is out of the office. I advocate learning constantly for those of us in technology. This might be especially important for data professionals as we are at the nexus of so muc activity these days. Data is important, our systems are under scrutiny for security, there is an every growing set of applications that want to connect, and we often struggle to keep up in a fast-paced, DevOps world. It feels like my plate is overflowing with the need to understand PowerShell, JSON, the cloud, Hadoop and its myriad of related technologies on the Microsoft Stack, machine learning, data science, statistics, and when I have time, make sure I'm up to date with SQL. Recently I've run across a few pieces that ask the question about where to spend time. There's a [blog on R v Python]( (I've seen quite a few of these) and a thread on deciding [if ML skills are something a database developer should learn](. These are just a few of the ones I've seen in the last month, as I guess lots of people are thinking and writing about New Year resolutions and goals. We have a limited amount of time for learning each day, week, month, or year. We might be able to increase that for a short time, perhaps months, but all of us need some balance in life. Given that there's a limited amount of time, and that we need some focus and practice over time to absorb skills, where do you spend your effort in learning? Jeff Moden is an advocate of you sharpening your T-SQL skills with deep learning, as he sees this as one of the main reasons that applications perform poorly (and people post in our forums). Others lean towards adding new skills, perhaps adding SSIS proficiency or transitioning your analysis skills to Extended Events. Still others might seek to become more generally rounded by spending time on C# or Python. There are also other views, such as focusing on the skills you need in your current position. If you're not happy, maybe you want to devote learning effort to get a job in an area where you're passionate. Or maybe you just want to make more money, and are willing to spend time in those technologies that are paying more. The choice is up to you, and I can't give you the answer that suits your path. You must find a balance between work and the rest of life, and make the choices that best fits you. I don't want to imply this is easy. Thinking about the things that really matter to you and affect how you approach each day is a difficult exercise. My advice is that you go in one of two ways. If your career is something that you feel passionate about, focus your learning in a way that will help you find a job that brings you purpose. If you just need a job to pay the bills, learn things that increase your earnings, but ensure that you have family, faith, hobbies, or some other endeavor outside of work that brings purpose and fulfillment to you. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [Loading Data using R and PowerBI]( nick.dale.burns from SQLServerCentral.com I'm not a fan of R visuals in PowerBI. I recently learned I can use R to load data and native PowerBI visuals to display the data. Here's how. [External Article]( [Simple Reporting with Flyway and Database Models]( Additional Articles from Redgate If you can generate a file-based (JSON) model for each new version of a database, produced by a Flyway migration, then you have an easy way to run simple reports to help you search, list, and understand the structure of these databases. I'll show how to produce the models using PowerShell and then run some queries against them to generate the reports. [External Article]( [Memory profiling in Python with tracemalloc]( Additional Articles from SimpleTalk You can find memory leaks in Python code with tracemalloc. In this article, Priyanka Nawalramka demonstrates how to use tracemalloc to find the leaks. [Blog Post]( From the SQL Server Central Blogs - [Data Engineer Diary: How to make Power BI Gateway work in an environment with a proxy]( Joyful Craftsmen from Joyful Craftsmen Blog With this blog post, I would like to show and describe to you the last lesson that I learned on my recent project regarding the installation and configuration of... [Blog Post]( From the SQL Server Central Blogs - [KQL Series – Interactive Analytics with Azure Data Explorer]( HamishWatson from The Hybrid DBA's Blog This blog is about how we can do interactive analytics with Azure Data Explorer to explore data with ad hoc, interactive, and lightning fast queries over small to extremely...   Question of the Day Today's question (by Steve Jones - SSC Editor):  Starting on Monday How do I ensure that the T-SQL code I run recognizes Monday as the first day of the week? Think you know the answer? [Click here]( and find out if you are right.    Yesterday's Question of the Day (by Steve Jones - SSC Editor) Creating AAD Users in Azure SQL Database I want to create a user in an Azure SQL Database that will only authenticate in the database and not from the logical master database. This user is an AAD user in my Azure domain. What code is appropriate to create this user? Answer: CREATE USER [bob@contoso.com] FROM EXTERNAP PROVIDER Explanation: The CREATE USER with the EXTERNAL PROVIDER option will allow you to this. Ref: CREATE USER - [Discuss this question and answer on the forums](    Database Pros Who Need Your Help Here's a few of the new posts today on the forums. To see more, [visit the forums](. --------------------------------------------------------------- SQL Server 2017 - Administration [AG backup preference behavior]( - Hi All, We have 3-node AG setup in our Production environment. Primary site (2 nodes), (DR site 1node). SQL Version is :Microsoft SQL Server 2017 (RTM-CU23) Enterprise Edition:(64-bit) We have 3 user databases as part of AG. We had some performance issues and decided to offload the backup load to secondary node of AG. For […] [Named instance connectivity]( - Hi We have a new named instance, I can able to connect using SSMS. However when I ping the instance name, it’s not working. It is enabled for mixed mode & enabled for the remote connections. TCP/IP port is enabled.  I added the 1434 udp port in the firewall. Also changed the dynamic port to […] SQL Server 2017 - Development [How to validate user if input formate no yyyy-MM-dd for created date ?]( - I work on sql server 2017 i need to validate user input of temp table if formate of created date not YYYY-MM-DD then reject by update status field of temp table to be Notvalid and if formate of created date is YYYY-MM-DD then update status by Valid CREATE TABLE #TempPC ( [ID] INT IDENTITY , […] [Adding leading zeroes after the decimal]( - Hello, I have the following requirement to add leading zeros after the decimal from numeric string, please advice the same. Input -> OutPut 100 -> 100.0000 1.1 -> 1.0001 1.10 -> 1.0010 1.100 -> 1.0100 200.00 -> 200.0000 Administration - SQL Server 2014 [StartDate & End Date parameters]( - Hi, I've created a view to populate data from existing tables In my report project I built using SSDT 2014, I created Dataset to read form this view. I also set a Startdate and Endate parameter with default calander values. The expectation is for the dates to filter the dates by above parameters. The Report […] Development - SQL Server 2014 [Pivot - Unpivot]( - Hi everybody, i have a table in SQl Server 2014 like the following: And i woul like to abtain an output like this: any help is appreciated. Thank you [Pivot Database Names]( - Hello, I have been trying and failing for days to create a query, or SSRS report, that makes a column for each database on the server, with each row representing a logon. The values of the database named columns would be the database role that logon has in the database. I have been trying with […] SQL 2012 - General [SQL Server 2012 Standard Media/ISO]( - Does anyone have a copy of SQL Server 2012 Standard handy? I just need the ISO or DVD contents to install on a new server. I have the key already from the old server, but we don't have the media any longer. I checked online and can't seem to find a good place to download […] SQL Server 2019 - Development [Using RTRIM, LTRIM with a LEFT Function.]( - Is there a particular order for using the LTRIM, RTRIM with a LEFT function? Example - I only want the Left 35 from a field and want to trim any unnecessary off it. Is this the correct Syntax - LEFT(RTRIM(LTRIM(Guar.First)),35) or should I use the TRIM's then the LEFT? [Newbie Question:If else Sql Question]( - In this case i am expecting the else statement to execute. please correct dECLARE @BusinessEntityId INT SET @BusinessEntityId=1003 IF @BusinessEntityId<=(SELECT MAX(BusinessEntityId) AS BusinessEntityId FROM Person.Person) BEGIN SELECT * FROM Person.Person WHERE BusinessEntityID<=@BusinessEntityId END ELSE IF @BusinessEntityId>=1000 AND @BusinessEntityId<2000 SELECT * FROM Person.Person WHERE BusinessEntityID>=@BusinessEntityId AND BusinessEntityID<=@BusinessEntityId SQL Azure - Development [Troubleshooting a pipeline error]( - I am creating a pipeline in a data factory per this page's instructions, . I am a beginner with this and am trying to follow the instruction as closely as possible. The change, in my case, is that I am using a VM SQL installation, also in Azure, but not a managed instance. I have […] Amazon AWS and other cloud vendors [Migrating Existing AWS Infrastructure into CM?]( - Have an old EC2 environment with dozens of Classic ELB's & AutoScale Groups/Launch Configs/CloudWatch Alarms/Policies/SecurityGroups etc, with different specifics etc. AWS is discontinuing Classic range in coming months I believe. So looking to transfer it all to App/Network ELB's. It'll be a combination of things be recreated eg. new ELB's and things being reused eg. […] [Migrating Existing AWS Infrastructure into CM?]( - Have an old EC2 environment with dozens of Classic ELB's & AutoScale Groups/Launch Configs/CloudWatch Alarms/Policies/SecurityGroups etc, with different specifics etc. AWS is discontinuing Classic range in coming months I believe. So looking to transfer it all to App/Network ELB's. It'll be a combination of things be recreated eg. new ELB's and things being reused eg. […] Reporting Services [SSRS subscription reporting as "Pending"]( - I have a data-driven subscription which emails a report to 82 recipients, with the email addresses returned by the data-driven dataset. The SSRS logfile reports 82 successful email entries for this subscription but the subscription page shows the result as "Pending". Any ideas why the result isn't showing as successful or failed? General [Is SQL Express enough?]( - I am assisting a company in upgrading their server. It is a small business with 9 computers. The mostly have a few shared documents and spreadsheet on the server, and the server hosts and MS SQL database for their accounting software. The accounting software has a maximum of 6 users at a time. The database […]   [RSS Feed]([Twitter]( This email has been sent to {EMAIL}. To be removed from this list, please click [here](. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com. ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -