How to Create a Power BI report using MySQL data (2023-01-09)

SQLServerCentral Newsletter for January 9, 2023 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [How to Create a Power BI report using MySQL data]( - [Converting Data Across Time Zones: An In-Depth Primer]( - [Analyze Azure Cosmos DB data with Synapse Serverless SQL Pools]( - [From the SQL Server Central Blogs - Book Review – Designing Data-Intensive Applications]( - [From the SQL Server Central Blogs - Use the output of a Script activity as the items in a ForEach activity in Data Factory]( Question of the Day - [The Time Conversion]( The Voice of the DBA  Daily Coping Tip Look back at a previous coping tip that required planning and evaluate how it helped 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. Forgoing Tech Investments The US was hit with a number of storms over the Christmas holiday weekend. This disrupted air travel for many airlines and their customers, but one of the worst hit was Southwest Airlines. They accounted for most of the cancellations, over [half of their scheduled flights]( at one point. A number of places reported talking with Southwest employees who [blamed the lack of tech investment by Southwest over time](, noting this caught up with them. The Chief Operating Officer disagreed, saying that their scheduling system is the best in the world, even as the CEO noted [that their scheduling software couldn't keep up](and they fell back to manual operations. Most of us likely have no idea of how Southwest software works or the scope of the problem. This airline does tend to operate differently than many others in that they mostly fly point to point, rather than using hubs. Possibly they have the best point-to-point scheduling software in the world, but it still couldn't keep up with the storms covering much of the US. There's an [interesting perspective on Facebook](, supposedly from a pilot with 35 years of experience with SouthWest. If you don't want to click, his view is the hands-on CEO retired years ago and accountants were appointed as CEO and COO. They improved the money flow, but neglected investments in tech and weren't aware of how the business really runs day to day. The infrastructure and software deteriorated, and they've had many small issues, but issues that were bigger than other airlines. They've started turning around with a CEO that is more hands-on, but they're digging out of a hole. Like many of you, I've built and operated software over the years. I sometimes realize just how hard it can be to keep up with the demands of customers for adjusting how our systems work. I also know that it's easy to slow your investment in a system that appears to works and limit your efforts to just maintenance work. [Allan Hirt wrote about this](. This does bring up the issue of investing in systems and maintaining them over time. I see why many companies would prefer to purchase software and let someone else manage the investment in ongoing development. I also know that for companies that see software as strategic, likely there needs to be regular investment, upgrading and refactoring code, as well as finding ways to scale higher and use resources more efficiently. Especially for databases. The battle between enhancing software and reducing technical debt is a constant one. I see this struggle being one that project managers and developers never agree on, but in the companies that seem to thrive, there is a balance. Perhaps it's splitting the sprints, perhaps it's allocating regular time during each development period, or maybe there's another way. One thing is certain. We need to find a balance. Otherwise we might get into the situation where a complete rewrite or replacement of software is warranted; a situation that is almost always very costly. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [How to Create a Power BI report using MySQL data]( Daniel Calbimonte from SQLServerCentral Introduction In this article, we will learn to create a Power BI report using MySQL data. The article will teach us to use the MySQL Workbench (something like the SSMS, but for MySQL). We will do the following in this article. First, we will connect and create a database in MySQL. Secondly, we will create a […] [External Article]( [Converting Data Across Time Zones: An In-Depth Primer]( Additional Articles from SimpleTalk If only the entire world used UTC, wouldn’t life be so much easier? We can dream, can’t we? While some software applications can live in an ecosystem where all dates and times can be stored in a single time zone with no conversions needed, many cannot. [External Article]( [Analyze Azure Cosmos DB data with Synapse Serverless SQL Pools]( Additional Articles from MSSQLTips.com In this tip, we guide you through the setup of Azure Synapse Link so you can query Azure Cosmos DB data with the Synapse Serverless. [Blog Post]( From the SQL Server Central Blogs - [Book Review – Designing Data-Intensive Applications]( Koen Verbeeck from Koen Verbeeck The book Designing Data-Intensive Applications: The Big Ideas Behind Reliable, Scalable, and Maintainable Systems by Martin Kleppmann was recommended to me by a colleague. The author has worked at companies... [Blog Post]( From the SQL Server Central Blogs - [Use the output of a Script activity as the items in a ForEach activity in Data Factory]( Meagan Longoria from Data Savvy In early 2022, Microsoft released a new activity in Azure Data Factory (ADF) called the Script activity. The Script activity allows you to execute one or more SQL statements...   Question of the Day Today's question (by Steve Jones - SSC Editor):  The Time Conversion On SQL Server 2022, I have this code. DECLARE @t VARCHAR(20)= '24:33:12'; SELECT TRY_CAST( @t AS TIME) What does this return? 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) The Serverless Failover Database I have an Azure SQL Database provisioned as Serverless. When I enable a Failover group, what does this mean for the automatic options of this database? Answer: Auto-scale but not auto-pause Explanation: You still get auto-scaling with a failover group, but not auto-pause. Ref: Auto-pausing and auto-resuming - [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 2016 - Development and T-SQL [Where clause filter view before dense rank]( - I have a view that has a dense rank function on a cte, sorta like this: drop view if exists drview; go create view drview AS with cte as ( select * from (values ('1', '1', 'N'), ('1', '1', 'S'), ('1', '2', 'N' )) v(field1, field2, field3) ) select * , dense_rank() over ( partition […] [Identify mismatched pair sequences in two datasets]( - Hi All, I’ve got two datasets which should match pairs perfectly. There are however some pairs which have their sequences swapped around. I’d like a way to identify these mismatched pairs (swapped around). CTE? Please see the below code which replicates the issue as well as below screenshots. Thanks in advance for the help. SSQ […] SQL Server 2019 - Administration [bcp out from sql table rows are breaking divided into rows two or three rows]( - Hi All, I am doing BCP out to text file notepad, looks like some rows are getting divided into two or multiple rows. Is there anything I need to add in the code to format it. I have comma some special characters inside the field. Ex. This test is for, dba team @, got success […] [Request to Estimated time to apply these commands,]( - Good morning all I am currently working on the development of an ssrs report to supervise a transactional replication in production I used this request to retrieve the number of commands that are not delivered to the subscriber per publication IF EXISTS(SELECT * FROM sys.objects WHERE name = 'MSdistribution_history') BEGIN SELECT top(200) CONVERT(CHAR(15), time, 121) […] [Create a Job Schedule that runs on the 1st of month only if it falls on a Sunday]( - This job already has one schedule that runs on the first of the month. However, we need to run the job on a different schedule if the first of the month falls on a Sunday. It appears to me that the SQL Server Agent Job Properties Scheduler GUI is not flexible enough to handle this […] SQL Server 2019 - Development [Cannot alter database on Azure]( - Hi there We have created a database (empty) on Azure called 'Logger_Groups_CI' We then created a user called test2 as follows: -- add login CREATE LOGIN [test2] WITH PASSWORD='XXXX'; -- add user CREATE USER [test2] FROM LOGIN [test2] WITH DEFAULT_SCHEMA=dbo; then i added the user to the roles [dbmanager] and [loginmanager] as follows: -- add […] [Passing Multiple Choices to a Stored Procedure]( - In my stored procedure, I have something like WHERE W.Warehouse=@WH1 It works well when it is simple as EXEC dbo.MyProcedure @WH1='S0' I need to select multiple warehouses.  For example. EXEC dbo.MyProcedure @WH1='S0' OR W.Warehouse='T0' OR W.Warehouse='T0' OR W.Warehouse='Z0' I would expect that the prefix of first warehouse would be handled in the procedure so after […] SQL Azure - Development [How to list sum of values in the specific period]( - Sorry for very newbie questions. I have a table, shown below with values about duration of events. Sometimes I have several events in one hour/day. Instead of showing each particular event, I would like ot have sum of duration of all events on hourly/daily basis. How to achieve that? SELECT * FROM [dbo].[switches] WHERE previousevent='on' […] SQL Azure - Administration [Create table Partition on an existing table in synapse]( - I have a table in Synapse with huge volume of data and planning to create a partition by year wise with out dropping the table or data. I have seen many examples of creating a new table with partition but never came across of Alter table statements with add partition .So my question is ..is […] Amazon AWS and other cloud vendors [Step functions trigger rule]( - Been searching online but I cannot seem to find the answer. Is it possible to run the downstream task if and only if the upstream task failed? Something similar to a trigger rule in airflow. Thank you. Analysis Services [Visual studio, SSAS and stored procedure]( - I am trying to get data from a stored procedure into my SSAS Tabular model project. This stored procedure accepts few parameters. Is there away to supply the parameters at run time without hard coding it? Job Postings [Looking for SQL Architect]( - My company is looking for a SQL Architect Here's a summarized version of the job posting, but you can get more details on the link below. It's a really nice company and I can totally vouch for it. About the role Major Responsibilities Be an exceptional corporate citizen. Actively contribute as a member of the […] SQL Server 2022 - Development [What is the syntax for comparing a null value to another value in SQL?]( - To compare if a value is null in an IF condition in SQL, you can use the IS NULL operator. For example: IF myTable.custId IS NULL THEN ... END IF; This will evaluate to true if the custId column in myTable has no value. If there is a value in the custId column and you […] [Number rows from a value in one column]( - Hi! I want to number rows from a value in one column (apar_id). Table Apar_id  Amount 10     200 10     100 11     100 12     300 12     200 12     500 Want this result from my query Number Apar_id […] [Joining two columns of foreign key with one column of another table]( - Hi,  I have two columns in table1 i.e. hotel1 and hotel1 . Both of them have foreign key of second table2 hotels. i.e. Table 1 (Book) hotel1   hotel2 1        3 Table 2 Hotels 1   HotelName1 2   HotelName2 3   HotelName3  I want […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -