Business Intelligence Developer & Author
Spelunking, for those who don’t know is exploring caves. In some ways, that’s what it’s like to find your way around in databases in some organizations. It’s especially difficult without a flashlight.
This PowerPoint presentation will provide some tips, techniques, and several scripts that can help shed light on your data cavern.
The attached ZIP file has all of the scripts mentioned in the presentation.DatabaseSpelunking DatabaseSpelunkingScripts
Thanks to all who joined in the SQL Style presentation at the Tampa SQL Saturday. It was great to have folks involved during a presentation. It doesn’t look like I can attach the presentation, but it’s available on the SQL Saturday website. The queries are right here:
–Example Queries
use Fruit
go
select * from colors
select * from fruit
–Use ‘where’ clause
select *
from fruit
where colorKey = 1
–Dealing with null
select *
from fruit
–where colorKey = null
where colorKey is null
–Wildcard
select *
from fruit
where fruitName like ‘%e%’
–where fruitName like ‘%e ‘
–where rtrim(fruitName) like ‘%e’
–‘In’ clause
select *
from fruit
where fruitName in (‘apple’,’grape’)
–In with a subquery
select *
from fruit
where fruitName in (select fruitName from fruit where rtrim(fruitName) like ‘%e’)
–Everything from inner join
select *
from fruit f
inner join colors c
on f.colorKey = c.colorKey
–Relevent columns from inner join
select f.fruitName, c.color
from fruit f
inner join colors c
on f.colorKey = c.colorKey
–Everything from left outer join
select f.fruitName, c.color
from fruit f
left outer join colors c
on f.colorKey = c.colorKey
–Everything from right outer join
select f.fruitName, c.color
from fruit f
right outer join colors c
on f.colorKey = c.colorKey
–Everything from Cross join (match everything with everything – no ‘on’ condition)
select f.fruitName, c.color
from fruit f
cross join colors c
–on f.colorKey = c.colorKey
–Everything from full join (combination of left & right outer joins)
select f.fruitName, c.color
from fruit f
full join colors c
on f.colorKey = c.colorKey
–Check for Exists
select fruitName
from fruit f
where exists
(select *
from colors c
where c.colorKey = f.colorKey)
–Alternate version of Exists
select fruitName
from fruit f
left outer join colors c
on c.colorKey = f.colorKey
where f.colorKey is not null
–Aggregations
select colorKey, count(*) as cnt
from fruit f
group by colorKey
having count(*) > 1
–Self join
use [AdventureWorksDW]
go
select c.AccountDescription, p.AccountDescription
from DimAccount c
left outer join DimAccount p
on p.ParentAccountKey = c.AccountKey
order by c.accountKey
I’ll be speaking at the Tampa Bay BI User Group meeting on 11/6. The topic will be ‘Database spelunking’. Spelunking, for those who don’t know is exploring caves. In some ways, that’s what it’s like to find your way around in databases in some organizations. It’s especially difficult without a flashlight.
This PowerPoint presentation will provide some tips, techniques, and several scripts that can help shed light on your data cavern. DatabaseSpelunking
Here’s a zip file with the scripts that are mentioned in the presentation: DatabaseSpelunkingScripts
It’s not uncommon to have a commonly used table copied into a number of different databases, potentially on different servers. We had a requirement recently to change the schema of a commonly used table, but had no idea of where it’s used. Rather than manually inspect every single SQL Server in the organization, I whipped up a PowerShell script to ask each database on each server if it has a copy of the desired table.
It works great, but it’s a bit crude. I didn’t include much in the way of error handling, but given how rarely this will be used, it’s probably not worth doing. It does give you the location of every copy of the table that you specify at the beginning of the script! Don’t forget to update $serverList with the names of your servers.
Clear-Host
Write-Host “Searching SQL Servers….”
#Set up the variables
$tblName = “Put table you want here”
$serverList = ‘server1′,’server2\instance’,’server3′,’server4′
$SQLServer = “”
$SQLDBName = “”
$uid =””
$pwd = “”
$db = “”
$tbl = “”
$connectionString = “”
foreach($SQLServer in $serverList)
{
#create a connection to the database
$connectionString = “Server=$SQLServer;Database = Master;Integrated Security=SSPI;”
#open the connection
$dbConn = New-Object System.Data.SqlClient.SqlConnection
$dbConn.ConnectionString = $connectionString
$dbConn.Open()
Write-Host “Server: ” $SQLServer
#Compose a SQL statement to find every database
$qry = @”
select d.[name] as dbName
from sys.databases d
where d.[name] not in (‘master’,’tempdb’,’model’,’msdb’)
“@
#Add the query to the connection
$dbCmd = $dbConn.CreateCommand()
$dbCmd.CommandText = $qry
#Execute the query
$dbList = $dbCmd.ExecuteReader()
#Format and display the results
$dbs = new-object “System.Data.DataTable”
$dbs.Load($dbList)
#Check each database for desired table
foreach($db in $dbs)
{
$dbName = $db[0]
#Write-Host “Checking database: ” $dbName
#open the connection
$tblConn = New-Object System.Data.SqlClient.SqlConnection
$tblConn.ConnectionString = $connectionString
$tblConn.Open()
#Compose a SQL statement to find every table
$qry = @”
select s.[name] as schemaName
, tbl.[name] as TableName
from $dbName.sys.tables tbl
left outer join $dbName.sys.schemas s
on s.[schema_id] = tbl.[schema_id]
“@
#Add the query to the connection
$tblCmd = $tblConn.CreateCommand()
$tblCmd.CommandText = $qry
#Write-Host “Query: ” $tblCmd.CommandText “`n”
#Execute the query
try {
$tblList = $tblCmd.ExecuteReader()
}
catch
{
Write-Host ” -Error accessing database” $dbName
}
$tbls = new-object “System.Data.DataTable”
$tbls.Load($tblList)
#Write-Host ($tbls | Format-Table | Out-String)
foreach($tbl in $tbls)
{
if($tbl[1] -eq $tblName)
{
Write-Host “Found table in ” $dbName, $tbl[0], $tbl[1]
}
}
}
}
It’s surprising to find out how few organizations have a list of all of their SQL Server instances. In the past, building such a list was an onerous task. It turns out that there’s a great way to to find out about all of the SQL Server instances that a given server can see. Just ask SQL Browser!
As PowerShell can reach into the .Net object model, it’s a great platform for simple tasks like this. To use it, you need to remote to a desired SQL Server, start PowerShell, and paste in this command:
[System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources()
This will produce a list of servers along with the instance name (if any) and the server version number.
It’s a simple solution to an annoying problem!
One of the basic concepts that makes Business Intelligence work so well is the separation of data into facts (what is being analyzed) and dimensions (how it is analyzed) and organizing those facts and dimensions into a Star Schema. An illustration from my book ‘Business Intelligence Team Handbook’ shows how tables are organized into a star shape.
Star Schema
In this case, you can see how the Sales table is used as the fact table. I was explaining the concept of how dimension tables could be re-used in association with a variety of fact tables to build multiple star schemas. Many of the same dimensions referenced in the star schema above could be used with an Inventory fact table or perhaps with a Foreclosure fact table.
It occurred to me that the resulting design would be a ‘Constellation Schema’. This phrase creates a great mental image of how a Business Intelligence database is organized. It’s more complex than a Star Schema. It’s clearly NOT a Snowflake Schema (thank goodness). It’s a collection of stars.
This is one term that I’m adding to my vocabulary!