The Data Migration Assistant is a great tool developed to assess your SQL Server environment for a migration to a modern SQL Server platform such as SQL Server 2017 or Azure SQL Database. If you’re assessing a single SQL Server with only a few databases, reading the exported results (which can be in .JSON or .CSV format) may not be that difficult. But if you’re assessing dozens of environments including hundreds of databases, we need a better way to understand the results. Continue reading Making Sense of Data Migration Assistant Assessments with Power BI
Category Archives: SQL Server Tools
My Top Four Books for the MS Business Intelligence Professional
As a Business Intelligence Consultant, I do a decent amount of speaking, interacting with the community, and have written and contributed on a few SQL Server books. A question I’m often asked is if I can recommend any good books which brings me to this blog post. I wanted to make you aware of four books for learning data warehousing and other MS BI technologies that I’ve found incredibly helpful over the years I’ve spent designing and implementing enterprise data warehouse and business intelligence solutions. Continue reading My Top Four Books for the MS Business Intelligence Professional
TSQL Script to Find Foreign Key References to a Given Column
It’s always kind of a pain to have to hunt down all those foreign key references so you can address the issues. So I put this script together (based on a script found on StackOverflow) in order to help me find all the required information related to a particular column in a specified table. I’m mostly posting this for my own reference later and for anyone else that may find this useful, so enjoy!
SELECT OBJECT_NAME(f.object_id) as ForeignKeyConstraintName, OBJECT_NAME(f.parent_object_id) TableName, COL_NAME(fk.parent_object_id,fk.parent_column_id) ColumnName, OBJECT_NAME(fk.referenced_object_id) as ReferencedTableName, COL_NAME(fk.referenced_object_id,fk.referenced_column_id) as ReferencedColumnName FROM sys.foreign_keys AS f INNER JOIN sys.foreign_key_columns AS fk ON f.OBJECT_ID = fk.constraint_object_id INNER JOIN sys.tables t ON fk.referenced_object_id = t.object_id WHERE OBJECT_NAME(fk.referenced_object_id) = 'your table name' and COL_NAME(fk.referenced_object_id,fk.referenced_column_id) = 'your key column name'
Here’s a picture of what the results look like. I ran this query against the ReportServer database used for SSRS in case you were wondering.
If you want to find every Foreign Key in your database, just eliminate the Where clause to bring back all the FKs. Hopefully you found this as useful as I did.
Pragmatic Works Release Doc xPress Server Edition
This month marks the official release of Pragmatic Works’ Doc xPress Server Edition! While everyone knows that Doc xPress gives you the capability to document your SQL Server databases, SSIS packages, SSAS cubes, and SSRS reports in way like no other tool. But with the release of the Server Edition, you can now host your documentation to a hosted web application making it now easier than ever to share documentation, lineage, and data dictionary information across your organization. Imagine being able to provide your technical users as well as your business users a one-stop-shop to all of your organization’s technical documentation without requiring anything to be installed on their desktop! Doc xPress Server Edition provides that capability!
Being able to view your Doc xPress-generated documentation online means that your technical team can quickly and easily assess the impact of changes in your BI environment by conducting a thorough lineage and impact analysis. What SSIS packages, SSRS reports, or SSAS cubes will be affected if I change a single column? Now you know!
How many times have you wondered what your environment looked like in the past? With Doc xPress, you can periodically snapshot your environment and compare snapshots over time so you can quickly and easily assess any changes that have occurred. And now you can view the documentation in your web browser without the need for any desktop configuration or installation.
If Doc xPress Server Edition sounds interesting and you’d like more information on the software, Pragmatic Works is presenting a free online webinar on the technology July 30th at 1:00 pm EST! Head over to PragmaticWorks.com (you’ll need to scroll down a screen or two) to get signed up and registered for the event.