The Data Architect

What do Customer Service, Sales, Marketing, Manufacturing, Merchandising, Logistics, Legal, IT, Accounting, Finance, Administration, R&D, and Vendor Relations have in common? If you answered “data,” then you’re right. Data is water. It’s the only reason the plumbing, faucets, shower heads, toilets, hoses, and sprinklers matter. It permeates every aspect of the business, and without it, the business dies. The best businesses know this. And because they know it, the field of enterprise data architecture has steadily grown, flourished, and gained widespread respect over the last twenty years. But despite the mainstreaming of data architecture, many people still understand neither what it is that a data architect actually does for a living, nor what it takes to be a successful data architect. Today I’ll try to answer both.

Who is the data architect?

The data architect is equal parts teacher, preacher, organizer, ambassador, salesman, and tech guru. The data architect’s sworn duty is to protect and defend an organization’s data from all enemies, both foreign and domestic (ok, so that’s a little overblown; but it’s true, data is more often jeopardized by those within an organization than by those without). The data architect protects an organization’s data from being corroded, corrupted, coopted, or otherwise contaminated.

What does the data architect do?

The data architect plays a role in every organizational initiative that involves data – in other words, the data architect plays a role (or should) in every organizational initiative. From database design, to application development, from master data management to business intelligence, from CRM to ERP, every successful project will involve the data architect at some level. While typically part of the IT department, the data architect’s role is (and must be to be successful) very much inter-departmental. Though a non-manager, the data architect enjoys (in organizationally mature companies) an elevated status equal to them, and unfettered access to every level of the org chart, from the receptionist to the CEO.

What makes a good data architect?

Joe McKendrick (co-author of The SOA Manifesto, and an influential voice in the world of information technology) suggests that soft-skills (people skills) are a data architect's most important skills, followed closely by an unmatched level of expertise in the areas of methodology, modeling, and governance, and finally by a broad-based technical background that includes systems, networking, database development, application development, and project management. I've borrowed his top-five bullet points, and amplified them below with my own comments:  

1. McKendrick says a data architect should be “well-respected and influential.” To be successful, a data architect must have the gravitas needed to command respect, the soft-skills required to build consensus throughout an organization.

2. McKendrick says a data architect should be “able to emphasize methodology, modeling, and governance.” Because effective data management is predicated on the effective application of appropriate frameworks, methods, and principles, an effective data architect must have mastery of these.

3. McKendrick says a data architect should be “technologically and politically neutral.” I’ve long thought technological agnosticism a good thing. Because data architects typically have 15 to 20 years of experience, they naturally carry with them certain biases toward certain technologies (typically those with which they’ve spent the most time and built the most technical acumen), but they should be careful to let their personal preferences only inform, and never impeded, their objectivity. With regard to political neutrality, a data architect’s job is only and always to do right by an organization’s data; it is never to simply further the agenda of any employee, manager, or department. At the end of the day, the data architect’s goal is to do everything possible to ensure the closest possible alignment between organization's data with its stated business goals.

4. McKendrick says a data architect should be “articulate, persuasive, and a good salesperson.”

Data architects must be equally comfortable whether in the company of programmers or presidents – and equally conversant in the languages of each.

5. McKendrick says a data architect should be “enthusiastic.” Because data architects are given lots of responsibility, but very little authority, this is not a career for the faint-hearted or thin-skinned. Moreover, enthusiasm often sways option in a way that dry facts simply cannot; after all, the most compelling arguments are often apprehended as much as they are comprehended!

Well, I hope this has helped clear up just what it means to be a data architect. And as always, I'd love to hear your feedback! 

Putting Data Management in its Proper Place

The Intersection of Technology and Commerce
Business and technology are more interwoven today than at any other time in human history. It’s clear that the data driven nature of modern business is the result of an inexorable fusion of information technology and commerce. To paraphrase Bill Gates, for the first time ever, we can meaningfully discuss neither business nor technology independently of the other. In other words, Information Technology is the glue that binds together the manifold and disparate business functions of nearly every company.

Think about your own company; if you work for a large or mid-sized company, ask yourself what your Sales, Marketing, Purchasing, Logistics, Manufacturing, Accounting, Finance, and Human Resources departments have in common. If your company is successful, then your answer is most likely Information Technology. That’s because IT is the thread of commonality that runs through all the various—and otherwise unique—functional areas of most large and mid-sized companies. And while org charts generally show these departments as vertical silos that converge only at the top, today’s most successful companies are those best able to leverage IT to bring about meaningful convergence throughout their entire organizations—organizational convergence through effective Data Management.

The Progeny of Data and the Supremacy of its Management
The noted author Alvin Toffler famously said, “Data is not information, information is not knowledge, knowledge is not understanding, and understanding is not wisdom.” But organizational convergence allows a company to turn Toffler’s maxim on its head by presenting a “single version—single vision” of the enterprise where every department works from a single version of the company’s truth and toward a single vision of the company’s future. Wisdom comes from understanding, understanding from knowledge, knowledge from information, and information from data. IT departments are tasked with many diverse projects and activities like Systems and Network Engineering, Applications Development and Maintenance, and End User Support; while each of these is important, it is my strongly held opinion that so long as information, knowledge, understanding, and wisdom remain the progeny of data, Data Management will remain the sine qua non of Information Technology.


—James Sizemore

Resync Usernames and Logins after Database Move

Use sp_change_users_login to accomplish this. Here is a screen shot from my workstation with one way to do it (the way I do it). Any other methods or suggestions are welcome.

Finding Dependencies with Sysdepends

We are working on a a database "rethinking" project. As we go about the change process we want to make sure that as we "fix stuff" we don't break other "stuff." Here is a quick query I wrote using sysdepends to help ensure that we don't leave anything orphaned during our database revamping:

select b.name as ObjectName,
c.name as DependantObjectName,
c.xtype as DependantObjectType
from sysdepends a
inner join sysobjects b
on a.id = b.id
inner join sysobjects c
on a.depid = c.id
where b.name = 'YourStoredProcedureNameHere'
group by b.name, c.name, c.xtype

View Extended SQL Agent Job History

Use this query to generate job and job step history; you can see this information in the SSMS GUI, but this query will allow you to better play with (read: analyze) the information. Also, if you need to configure SQL Server 2005 to keep more history than the default of 100 records per job, you can modify the history tab under SQL Agent properties in SSMS. Here is the query:


select b.name as job,
a.step_name as step,
convert(varchar(10), cast(str(a.run_date,8, 0) as datetime), 111) as date,
stuff(stuff(right('000000' + cast (a.run_time as varchar(6 ) ) ,6),5,0,':'),3,0,':') as [time],
a.run_duration as duration_seconds,
a.run_duration / 60 as duration_minutes,
case         a.run_status
when 0 then 'failure'
when 1 then 'success'
when 2 then 'retry'
when 3 then 'cancelled'
when 4 then 'active'
end as disposition,
a.message as details
from         msdb.dbo.sysjobhistory a
inner join         msdb.dbo.sysjobs b
on b.job_id = a.job_id
-- {optionally, to filter by job name} where b.name = 'MyJobName'
order by         b.name,
a.run_date,
a.run_time

Post-modern greenbar.

How do I display a "greenbar" effect in my SQL Reports?


As popular as this "feature" is, and for as often as business users ask to have it included in their reports you would think Microsoft might actually include as a feature in SSRS; but they don't. 


To add this to tables in your reports add the following code to the Background Color property of the appropriate TableRow:


= iif (rownumber(nothing) mod 2 = 0, "Azure", "Transparent")


Obviously, feel free to substitute whatever values you prefer.