Friday, May 4, 2018 #

Power BI Presenting JSON data from SQL 2014 Table

SQL 2016 or later and SQL Azure both support JSON function in native SQL.  However, people using SQL 2014 or below are out of luck.

However, if we just need a quick report, PowerBI can provide that functionality.  Here are the steps:
  1. New PowerBI file >> Get Data
  2. Set up our SQL connection.  Because JSON parsing function is not supported natively, this connection cannot be Direct Query.  Even if we set it up as such, later steps will force PowerBI to change the connection type to "Import".
    Do not click "Load", click "Edit" to get into Query Designer
  3. In PowerQuery Editor, right-click on the column we want to parse JSON, select "Transform" >> "JSON"
  4. A new "expand" button will appear on the top right corner of column header.  Click on that and select the fields out of JSON we want to report on.  Then click "OK"
  5. When we are done with the dataset, "Close and Apply"
  6. The JSON fields are now available to report on.
Have fun!

Posted On Friday, May 4, 2018 9:54 AM | Comments (0)

SQL Remove Duplicates without Using Temp Table or Modifying Existing Table

Up until now, the way I understood to "fix" a table with duplicate data and no primary key or unique key is either:
  1. Modify the existing table, add a column and populate with unique key data, then delete off that unique key, or
  2. SELECT DISTINCT * INTO [a temp table] FROM [current table], TRUNCATE FROM [current table], INSERT INTO [current table] SELECT * FROM [a temp table]
I am not saying one way is better than the other.  In fact, if our table is huge and we are worried about transaction log file usage during this operation, option #2 is probably our best bet.  Just like everything else in BI, we need to understand the data first before developing.  That being said, the following way is fairly elegant in my option.  I have included the test script and screenshot of the result.  I have tested this in SQL 2014, 2016, and SQL Azure; all successful.


Test Code:

-- test delete duplicate rows from table without changing table

CREATE TABLE dbo.DeleteDupFromViewTest

       (FirstName VARCHAR(100) NOT NULL

       , LastName VARCHAR(100) NOT NULL)

GO

 

INSERT INTO dbo.DeleteDupFromViewTest

              (FirstName, LastName)

       VALUES

              ('FirstNameOne', 'LastNameOne')

              , ('FirstNameTwo', 'LastNameTwo')

              , ('FirstNameThree', 'LastNameThree')

              , ('FirstNameThree', 'LastNameThree')

              , ('FirstNameThree', 'LastNameThree')

              , ('FirstNameOne', 'LastNameOne')

              , ('FirstNameTwo', 'LastNameTwo')

              , ('FirstNameTwo', 'LastNameTwo')

              , ('FirstNameThree', 'LastNameThree')

              , ('FirstNameThree', 'LastNameThree')

              , ('FirstNameOne', 'LastNameOne')

GO

 

CREATE VIEW dbo.vw_DeleteDupFromViewTest AS

       SELECT

              FirstName

              , LastName

              , ROW_NUMBER() OVER(ORDER BY FirstName, LastName) AS RowID

       FROM dbo.DeleteDupFromViewTest

GO

 

SELECT * FROM dbo.vw_DeleteDupFromViewTest

 

DELETE t1

       FROM dbo.vw_DeleteDupFromViewTest t1

              LEFT OUTER JOIN (SELECT FirstName

                                                , LastName

                                                , MIN(RowID) AS RowID

                                         FROM dbo.vw_DeleteDupFromViewTest (NOLOCK)

                                         GROUP BY FirstName

                                                , LastName

              ) t2

                     ON t1.FirstName = t2.FirstName

                           AND t1.LastName = t2.LastName

                           AND t1.RowID = t2.RowID

       WHERE t2.FirstName IS NULL

 

SELECT * FROM dbo.vw_DeleteDupFromViewTest

 

SELECT * FROM dbo.DeleteDupFromViewTest

 

DROP VIEW dbo.vw_DeleteDupFromViewTest

DROP TABLE dbo.DeleteDupFromViewTest


Screenshot of result:



Posted On Friday, May 4, 2018 9:11 AM | Comments (0)

Friday, January 26, 2018 #

Having trouble using GitHub with BI projects

I had existing solution built.  I've attached a screenshot of local repository folder structure.

So to get started in GitHub, I created a company repository called "DataServices", and uploaded the entire code structure through the code >> Upload files functionality.

I was happy to see that my SQL project was showing connection.  However, whenever I make a change, it would show the red checkmark for checkout while the file is being edited.  As soon as I save the file, that red checkmark would go away and be replaced by a blue lock sign, even though the change has not been committed or sync to the master branch.  There is no option on any of these files to right-click and commit.  When I go to Team Explorer and click on "Changes", it does not detect any changes.  I can disconnect and reconnect to the repository, it is still the same thing.  However, after I close visual studio and reload, it would show the changes (see screenshot)

In my SSIS project, it's even worse.  It looks like on the solution file was connected to GitHub, and nothing else.  See the second screenshot.

Just to make sure, I created a blank solution and selected "Add to repository" through visual studio, and created code into a brand new respository, then added some packages and check in the code.   The result is the same.

So at this time, I can't check in the code without having to use the website and "upload files".  Our team cannot sync up as we don't know who has what checked out.

Need some expert to tell us what's wrong, what to try, and how to save this.

Posted On Friday, January 26, 2018 9:34 AM | Comments (1)

Thursday, June 29, 2017 #

SQL Azure as part of availability group, but not really doing any real work

Wouldn't it be nice to have SQL Azure as part of high availability group, but not really doing any real work?  I'm looking for a set up of Active/ReadOnly/DR-Azure.  So far I have no luck finding information on how to implement this.

UPDATE:  SQL 2017 allows SQL on Linux to be part of HA group without having to use clustering service!

Posted On Thursday, June 29, 2017 11:38 AM | Comments (0)

SSMS 17.1 missing features

Just noticed that SSMS 17.1 (current version) does not support drag and drop of a SQL file.  Reverted back to SSMS 17 RC3 and that feature is back.  Going to check on other features.

Posted On Thursday, June 29, 2017 10:30 AM | Comments (0)

Copyright © Kevin Shyr

Design by Bartosz Brzezinski

Design by Phil Haack Based On A Design By Bartosz Brzezinski