Category Archives: Tech Notes

Calculate Weighted Average in Excel using SumProduct() Function

2016-12-14 Jon Culp Leave a comment

Weighted averaged, also known as weighted arithmetic mean, is similar to an ordinary average, except that instead of each of the data points contributing equally to the final average, each data point is “weighted” and thus contributes more or less depending on the given weight. The weight would typically be some correlated data point that indicates significance of the value being averaged.

For example, let’s say we were tracking the progress of a project and its various tasks. Our data set includes the task number, percent completed, and estimated hours to complete the task. We want to calculate an overall percent completed for the project based on how complete the individual tasks are. Take a look at the example below.

=AVERAGE(B2:B6)

If we calculate a straight average of the percent complete column then we get 75% completed overall. However, this could be deceiving because some tasks will take longer to complete than others, as indicated in the estimated hours column. Let’s use this estimated hours column as the “weight” in our weighted average.

One way to do this would be to:

multiply the item (% Complete) by the weight (Estimated Hours) at the row level, shown in column D below.
sum up all of those products in column D, and then
divide by the sum of the weight for all records (column C)

Formulas:

Results:

The result of the weighted average is 49%, which is much different than the 75% from a straight average. This is because there are items which have a high percent completed but a low estimated hours to complete, and also items with low percent completed and higher estimated hours to complete. By including a weight to factor in the level of effort for each item you get a much more accurate result.

What’s the problem with this approach? If the number of tasks changes then it becomes a fairly manual task to adjust the rows and formulas accordingly. Also, I don’t like the idea of having a “helper” or “work” column inserted into the data set. There is a quicker and simpler way to calculate the weighted average than the method I just explained.

Continue reading Calculate Weighted Average in Excel using SumProduct() Function →

MS SQL Server 2008 R2

Collapse and Concatenate Rows

2016-07-13 Jon Culp Leave a comment

I had a need to concatenate and comma separate some multi-row data into an array of values with each having an unknown number of elements, in other words, take a many to one parent-child relationship and collapse the many child rows into the one parent record and separate the child record values with a comma. In the past, my default method to solve this problem was to build a temporary table and then use a loop to iterate through a data set and append the elements by updating rows in the temporary table, or use a common table expression with anchor and recursive members. Recently I stumbled upon the “stuff” and “for xml” functions. I had seen these functions before but never took the time to understand their potential use. These function can be used to solve the problem mentioned.

T-SQL For XML (Path Mode)

Function Description: A SELECT query returns results as a rowset. You can optionally retrieve formal results of a SQL query as XML by specifying the FOR XML clause in the query. The FOR XML clause can be used in top-level queries and in sub queries. The top-level FOR XML clause can be used only in the SELECT statement. In sub queries, FOR XML can be used in the INSERT, UPDATE, and DELETE statements. It can also be used in assignment statements.

In a FOR XML clause, you specify one of these modes: RAW, AUTO, EXPLICIT, PATH. We will only use PATH for this exercise.

The PATH mode together with the nested FOR XML query capability provides the flexibility of the EXPLICIT mode in a simpler manner.

The EXPLICIT mode allows more control over the shape of the XML. You can mix attributes and elements at will in deciding the shape of the XML. It requires a specific format for the resulting rowset that is generated because of query execution. This rowset format is then mapped into XML shape. The power of EXPLICIT mode is to mix attributes and elements at will, create wrappers and nested complex properties, create space-separated values (for example, OrderID attribute may have a list of order ID values), and mixed contents.

I will not list the syntax for this function because it can get pretty complex very quickly for all of the options. Instead, you can see in the example I just use it to concatenate the rows into a comma separated array. Any other XML is basically ignored by passing in the argument “Path (”)”.

(More information on For XML)

T-SQL Stuff Function

Function Description: The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.

Function Syntax: STUFF ( character_expression , start , length ,character_expression )

(More Information on the Stuff function)

Example Problem

List database users and include all roles of which a user is a member, separated by comma.

Solution using Stuff and For XML

“For XML” is used to collapse and concatenate the row data into a single array.
“Stuff” is used to remove the leading comma.
Note that we are using a correlated subquery when we reference “dp.principal_id” in order to limit our roles and role members to our database principals in the main outer query.

SELECT
dp.principal_id
,type_desc
,create_date
,modify_date 
,STUFF(( SELECT  ',' + CONVERT(VARCHAR(500),isnull(-- USER_NAME(mem.role_principal_id),''))
        FROM    sys.database_role_members mem
        WHERE   mem.member_principal_id = dp.principal_id
      FOR
        XML PATH('')
      ), 1, 1, '') AS Roles
from sys.database_principals  dp
where type != 'R'
and name not in ('dbo','guest','INFORMATION_SCHEMA','sys','MS_DataCollectorInternalUser')

SELECT

dp.principal_id

,type_desc

,create_date

,modify_date

,STUFF(( SELECT ',' + CONVERT(VARCHAR(500),isnull(-- USER_NAME(mem.role_principal_id),''))

FROM sys.database_role_members mem

WHERE mem.member_principal_id = dp.principal_id

FOR

XML PATH('')

), 1, 1, '') AS Roles

from sys.database_principals dp

where type != 'R'

and name not in ('dbo','guest','INFORMATION_SCHEMA','sys','MS_DataCollectorInternalUser')

Output:

principal_id	type_desc	create_date	modify_date	Roles
5	SQL_– USER	11/13/2002	6/19/2010	db_owner
7	SQL_– USER	11/13/2002	6/19/2010	db_owner,db_datareader,db_datawriter
8	WINDOWS_– USER	3/26/2009	3/26/2009	db_datareader
10	WINDOWS_– USER	3/16/2008	3/16/2008	db_datareader
11	SQL_– USER	7/11/2008	6/19/2010	WebViews
13	SQL_– USER	7/25/2014	7/25/2014	role_proxyusers
14	WINDOWS_– USER	3/12/2009	3/12/2009	db_datareader
16	SQL_– USER	7/25/2014	7/25/2014	role_proxyusers
18	SQL_– USER	8/26/2005	6/19/2010	db_owner
19	WINDOWS_– USER	6/2/2009	6/2/2009	MSReplPAL_9_1,db_datareader
20	WINDOWS_GROUP	10/3/2005	10/3/2005	db_datareader
21	SQL_– USER	7/14/2009	5/2/2016	role_proxyusers,db_datareader,db_denydatawriter

Databases, MS SQL Server 2008 R2, Tech Notes

Microsoft SQL Server Developer Edition is now free

2016-05-19 Jon Culp Leave a comment

https://blogs.technet.microsoft.com/dataplatforminsider/2016/03/31/microsoft-sql-server-developer-edition-is-now-free/

“Exciting news! Starting today, SQL Server 2014 Developer Edition is now a free download for Visual Studio Dev Essentials members (you will be prompted to sign in to Visual Studio Dev Essentials before you can download SQL Server 2014 Developer Edition). We are making this change so that all developers can leverage the capabilities that SQL Server 2014 has to offer for their data solution, and this is another step in making SQL Server more accessible. SQL Server Developer Edition is for development and testing only, and not for production environments or for use with production data.”

“SQL Server 2016 Developer Edition, when released later this year, will also be free. To learn more about the exciting new features in SQL Server 2016, read the datasheet.”

Databases, MS Azure SQL Database V12, Tech Notes

Create Read Only User in Azure DB

2016-03-10 Jon Culp Leave a comment

I have been struggling with creating a user who has read only access to an azure database and finally figured it out. The key is connecting to the right database in a new session when executing the t-sql commands. This is because you can not execute a “use” statement, change the database in the current session via the GUI, and you can not make named references to databases in your t-sql.

~~server_name.database_name.~~schema_name.object_name

--OPEN NEW SESSION TO MASTER DB
CREATE LOGIN ReadOnlyLogin
	WITH PASSWORD = 'ENTER STRONG PASSWORD'
;

CREATE -- USER ReadOnlyUser
	FOR LOGIN ReadOnlyLogin
	WITH DEFAULT_SCHEMA = dbo
;

--OPEN NEW SESSION TO MASTER DB

CREATE LOGIN ReadOnlyLogin

WITH PASSWORD = 'ENTER STRONG PASSWORD'

;

CREATE -- USER ReadOnlyUser

FOR LOGIN ReadOnlyLogin

WITH DEFAULT_SCHEMA = dbo

;

--OPEN NEW SESSION TO YOUR AZURE DB
CREATE -- USER ReadOnlyUser FROM LOGIN ReadOnlyLogin;
EXEC sp_addrolemember 'db_datareader', 'ReadOnlyUser';

--OPEN NEW SESSION TO YOUR AZURE DB

CREATE -- USER ReadOnlyUser FROM LOGIN ReadOnlyLogin;

EXEC sp_addrolemember 'db_datareader', 'ReadOnlyUser';

Validate user has read only permissions by connecting to database using these new credentials and executing something like “CREATE TABLE dbo.test (test datetime);”, which should return an error message indicating permission denied.

More information on managing database access and login security in azure: https://azure.microsoft.com/en-us/documentation/articles/sql-database-manage-logins/

Networking, Tech Notes, Uncategorized

WiFi Range Heat Mapping

2016-03-10 Jon Culp 3 Comments

I wanted to compare WiFi coverage of two different wireless access points and their various configurations in my house, and also check for dead spots. One quick and cheap way to do this that I have come across is using a free product called Ekahau HeatMapper.

Ekahau HeatMapper allows you to walk around your home or office and capture information about WiFi signals in various spots, and then the software aggregates this information into a banded topographical map.

Continue reading WiFi Range Heat Mapping →

MS SQL Server 2008 R2, Tech Notes

Drop Login and Users in MS SQL Server

2016-02-22 Jon Culp 1 Comment

This code will drop the login and users for all databases for a provided user name. and reports on its progress. This only works where the user and login match. This script does not check if users own any objects.

/*********************************************
DROP LOGIN AND -- USERS FOR ALL DBs IN A SERVER
Possible errors could be if a user is the owner of a database object.
*********************************************/
 
       SET NOCOUNT ON
       -- USE master
 
       --DECLARE VARIABLES
       DECLARE @Dropping_Windows_Logon_Name NVARCHAR(MAX) = N'starwars\amlegault' --SPECIFY -- USER TO DROP, typical format: domain\user
       DECLARE @tbl_Databases TABLE (Name NVARCHAR(MAX))
       DECLARE @Database_Name NVARCHAR(MAX)
       DECLARE @SQL_Drop_Login NVARCHAR(MAX)   
       DECLARE @SQL_Drop_DB_Users NVARCHAR(MAX)
      
 
       PRINT 'Begin Login and User Drop Procedure for Windows Logon Name ' + @Dropping_Windows_Logon_Name + ' on server ' + @@SERVERNAME
 
             
       --DROP LOGIN FROM SERVER
       PRINT CHAR(0x0D) + CHAR(0x0A) + 'Begin Server Principal Drop Procedure'
       IF EXISTS (SELECT * FROM sys.server_principals WHERE name = @Dropping_Windows_Logon_Name)
       BEGIN TRY
              SET @SQL_Drop_Login = 'DROP LOGIN [' + @Dropping_Windows_Logon_Name +']'
              EXEC (@SQL_Drop_Login)
              PRINT 'Server Principal Dropped'
       END TRY
       BEGIN CATCH
              PRINT 'Server Principal Found But Could Not Drop'
       END CATCH
       ELSE
              PRINT 'Server Principal Not Found'
      
 
       ---- USER DROP PROCEDURE
       PRINT CHAR(0x0D) + CHAR(0x0A) + 'Begin User Drop Procedure'
      
       --LOAD DATABASE NAMES INTO TEMP TABLE TO -- USE FOR LOOP
       INSERT INTO @tbl_Databases (Name)
       SELECT Name
       FROM master.sys.databases WITH(NOLOCK)
      
       --LOOP THROUGH EACH DATABASE AND DROP -- USER IF THEY EXIST
       WHILE EXISTS (SELECT TOP 1 * FROM @tbl_Databases)
       BEGIN
              SET @Database_Name = (SELECT TOP 1 Name FROM @tbl_Databases)
              SET @SQL_Drop_Login ='-- USE '+@Database_Name
              +' IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = ''' + @Dropping_Windows_Logon_Name + ''' AND sys.database_principals.type = ''U'')
              BEGIN TRY
                     DROP -- USER [' + @Dropping_Windows_Logon_Name +']
                     PRINT '' Dropped User in '+@Database_Name+'''
              END TRY
              BEGIN CATCH
                     PRINT '' User Found But Could Not Drop in '+@Database_Name+'''
              END CATCH
              ELSE PRINT '' User Not Found in '+@Database_Name+'''
              '
              EXEC (@SQL_Drop_Login)
              DELETE FROM @tbl_Databases WHERE Name = @Database_Name
       END
 
       PRINT 'End User Drop Procedure'
 
       --END
       PRINT CHAR(0x0D) + CHAR(0x0A) + 'End Login and User Drop Procedure for Windows Logon Name ' + @Dropping_Windows_Logon_Name + ' on server ' + @@SERVERNAME
 
       SET NOCOUNT OFF

/*********************************************

DROP LOGIN AND -- USERS FOR ALL DBs IN A SERVER

Possible errors could be if a user is the owner of a database object.

*********************************************/

SET NOCOUNT ON

-- USE master

--DECLARE VARIABLES

DECLARE @Dropping_Windows_Logon_Name NVARCHAR(MAX) = N'starwars\amlegault' --SPECIFY -- USER TO DROP, typical format: domain\user

DECLARE @tbl_Databases TABLE (Name NVARCHAR(MAX))

DECLARE @Database_Name NVARCHAR(MAX)

DECLARE @SQL_Drop_Login NVARCHAR(MAX)

DECLARE @SQL_Drop_DB_Users NVARCHAR(MAX)

PRINT 'Begin Login and User Drop Procedure for Windows Logon Name ' + @Dropping_Windows_Logon_Name + ' on server ' + @@SERVERNAME

--DROP LOGIN FROM SERVER

PRINT CHAR(0x0D) + CHAR(0x0A) + 'Begin Server Principal Drop Procedure'

IF EXISTS (SELECT * FROM sys.server_principals WHERE name = @Dropping_Windows_Logon_Name)

BEGIN TRY

SET @SQL_Drop_Login = 'DROP LOGIN [' + @Dropping_Windows_Logon_Name +']'

EXEC (@SQL_Drop_Login)

PRINT 'Server Principal Dropped'

END TRY

BEGIN CATCH

PRINT 'Server Principal Found But Could Not Drop'

END CATCH

ELSE

PRINT 'Server Principal Not Found'

---- USER DROP PROCEDURE

PRINT CHAR(0x0D) + CHAR(0x0A) + 'Begin User Drop Procedure'

--LOAD DATABASE NAMES INTO TEMP TABLE TO -- USE FOR LOOP

INSERT INTO @tbl_Databases (Name)

SELECT Name

FROM master.sys.databases WITH(NOLOCK)

--LOOP THROUGH EACH DATABASE AND DROP -- USER IF THEY EXIST

WHILE EXISTS (SELECT TOP 1 * FROM @tbl_Databases)

BEGIN

SET @Database_Name = (SELECT TOP 1 Name FROM @tbl_Databases)

SET @SQL_Drop_Login ='-- USE '+@Database_Name

+' IF EXISTS (SELECT * FROM sys.database_principals WHERE name = ''' + @Dropping_Windows_Logon_Name + ''' AND sys.database_principals.type = ''U'')

BEGIN TRY

DROP -- USER [' + @Dropping_Windows_Logon_Name +']

PRINT '' Dropped User in '+@Database_Name+'''

END TRY

BEGIN CATCH

PRINT '' User Found But Could Not Drop in '+@Database_Name+'''

END CATCH

ELSE PRINT '' User Not Found in '+@Database_Name+'''

EXEC (@SQL_Drop_Login)

DELETE FROM @tbl_Databases WHERE Name = @Database_Name

END

PRINT 'End User Drop Procedure'

--END

PRINT CHAR(0x0D) + CHAR(0x0A) + 'End Login and User Drop Procedure for Windows Logon Name ' + @Dropping_Windows_Logon_Name + ' on server ' + @@SERVERNAME

SET NOCOUNT OFF

MS SQL Server 2008 R2, Tech Notes, Uncategorized

Orphan Users in MS SQL Server

2016-02-22 Jon Culp Leave a comment

Checks all databases on a server for any users who do not have a correlated login, based on SID. Uses the undocumented command of sp_msforeachdb. You could put in a loop if you want to avoid this command.

DECLARE @orphan_users TABLE (Server NVARCHAR(MAX),DBName sysname, [user_name] sysname,type_desc nvarchar(60),default_schema_name sysname, create_date datetime, modify_date DATETIME, [sid] VARBINARY(85))
INSERT INTO @orphan_users (Server, DBname,[user_name],type_desc,default_schema_name,create_date,modify_date,[sid])
EXEC sp_msforeachdb
'use [?]
SELECT
@@SERVERNAME
,DB_NAME() DBname
,name
,type_desc
,default_schema_name
,create_date
,modify_date
,[sid]
from sys.database_principals where type in (''G'',''S'',''U'')
and [sid] not in ( select [sid] from sys.server_principals where type in (''G'',''S'',''U'') )
and name not in (''dbo'',''guest'',''INFORMATION_SCHEMA'',''sys'',''MS_DataCollectorInternalUser'')'
SELECT * FROM @orphan_users
--WHERE user_name LIKE '%SomeUser%' /*optional*/
ORDER BY user_name

DECLARE @orphan_users TABLE (Server NVARCHAR(MAX),DBName sysname, [user_name] sysname,type_desc nvarchar(60),default_schema_name sysname, create_date datetime, modify_date DATETIME, [sid] VARBINARY(85))

INSERT INTO @orphan_users (Server, DBname,[user_name],type_desc,default_schema_name,create_date,modify_date,[sid])

EXEC sp_msforeachdb

'use [?]

SELECT

@@SERVERNAME

,DB_NAME() DBname

,name

,type_desc

,default_schema_name

,create_date

,modify_date

,[sid]

from sys.database_principals where type in (''G'',''S'',''U'')

and [sid] not in ( select [sid] from sys.server_principals where type in (''G'',''S'',''U'') )

and name not in (''dbo'',''guest'',''INFORMATION_SCHEMA'',''sys'',''MS_DataCollectorInternalUser'')'

SELECT * FROM @orphan_users

--WHERE user_name LIKE '%SomeUser%' /*optional*/

ORDER BY user_name

Business Intelligence, Tech Notes

Publish Google Analytics Reports to the Web using Power BI

2016-02-22 Jon Culp Leave a comment

There is a feature in Power BI which allows you to connect to your google analytics data, and there is a new preview feature which allows publishing content to the web in an iframe. In this post I have combined both features together to display my website statistics to the web.

Connect to Google Analytics: https://powerbi.microsoft.com/en-us/documentation/powerbi-content-pack-google-analytics/
Publish to Web: https://powerbi.microsoft.com/en-us/blog/announcing-power-bi-publish-to-web/

Here is the result:
(View Larger)

Note: PowerBI has trouble rendering in Chrome in some cases. If you are using Chrome and can not see the dashboard then try another browser. Hopefully Microsoft will resolve this problem.

Business Intelligence, Tech Notes

Video: Business Intelligence with Power BI

2016-02-18 Jon Culp Leave a comment

I’ve been catching myself up on what Microsoft is doing lately regarding Business Intelligence. After spending days reading Microsoft documentation and testing some various configurations of Power BI, Gateway,Azure, and SSRS, I came across this video that goes over some history and then gets right into Power BI and some competing tools. I suspect Microsoft is working toward merging Power BI and SSRS based on some of their SQL Server public previews.

PostgreSQL 9, Tech Notes

Get Database Sizes in PestgreSQL

2016-02-18 Jon Culp Leave a comment

Returns the database name, owner, and size on a given server. Tested in PostgrSQL 9.2 and 9.3.

SELECT 
 d.datname AS Name
 ,pg_catalog.pg_get_userbyid(d.datdba) AS Owner
 ,CASE 
   WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
   THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
   ELSE 'No Access'
  END AS SIZE
FROM
 pg_catalog.pg_database d
ORDER BY
 CASE
  WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
  THEN pg_catalog.pg_database_size(d.datname)
  ELSE NULL
 END DESC

SELECT

d.datname AS Name

,pg_catalog.pg_get_userbyid(d.datdba) AS Owner

,CASE

WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')

THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))

ELSE 'No Access'

END AS SIZE

FROM

pg_catalog.pg_database d

ORDER BY

CASE

WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')

THEN pg_catalog.pg_database_size(d.datname)

ELSE NULL

END DESC

Source: https://wiki.postgresql.org/wiki/Disk_Usage

Jon Culp [.com]

Category Archives: Tech Notes

Calculate Weighted Average in Excel using SumProduct() Function

Microsoft SQL Server Developer Edition is now free

Create Read Only User in Azure DB

WiFi Range Heat Mapping

Publish Google Analytics Reports to the Web using Power BI

Here is the result:
(View Larger)

Video: Business Intelligence with Power BI

Get Database Sizes in PestgreSQL

An Information Archive for a Frivolous Human

T-SQL For XML (Path Mode)

T-SQL Stuff Function

Example Problem

Solution using Stuff and For XML

Here is the result: (View Larger)

An Information Archive for a Frivolous Human

Here is the result:
(View Larger)