September 30, 2015 at 6:56 am
Hey everyone,
I am a one-man BI shop for an organization with 600 employees. The organization is a tribal government, so there is a wide diversity of data sources, reports, and dashboards that need to be done. We are using the Microsoft BI stack (SQL Server, SSRS, SSAS, Datazen, PerformancePoint) to deliver the BI to the end users.
The end users here don't have a whole lot of exposure to BI so I get the "luxury" of essentially building the BI department from the ground up. So I built a couple of small data warehouses, and now the demand is growing quicker than I can service it by building small data marts. My next step is to build a centralized, tribal-wide data warehouse, but I also want to implement a standard error logging and data quality mechanism that supports custom business logic validation.
I figured that it would help me out quite a bit to build a template, and one of the vendors we are working with on a project suggested a meta-data driven template based system. I prefer to keep my ETL in T-SQL as opposed to SSIS (with the exception of pre-staging data from data sources such as SharePoint, flat files, and non Microsoft databases).
The big problem I am facing with all that is that I am having a hard time keeping my thoughts organized, and am constantly second guessing myself. In my head I envision something like this:
For each dim, having a stored proc to extract the data from the staging environment, process quality checks, write the failed rows to a data quality table, write the good rows to the dim table, and log any errors in an error table. Seems simple enough right? Yet everytime I open SSMS to start writing the procs I go full on stupid and completely freeze up because my mind races so far ahead of where I am at in the project.
Another thing that I'd like this new framework I am working to do is to be able to generate the CREATE scripts based on defined metadata so that all my objects have a consistent set of extended properties to provide metadata on what they are, where the data comes from, and what it means. The end goal is to be able to write an SSRS report that produces my data dictionary without having to manually create and maintain one. My initial thought would be to have a database where I can define my tables for the data warehouse in one table, and the corresponding columns in the other, then use dynamic SQL to print out the CREATE commands to build that database, add the extended properties, and populate those properties.
How do you guys plan out a project like this and document the design so that you can keep your head on straight while coding?
Thanks in advance!
September 30, 2015 at 7:13 am
Here is where I replied to your cross-post.
http://www.sqlservercentral.com/Forums/FindPost1724193.aspx
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply