Tag Archives: metadata mart

AI LLM New Project Roles including Prompt Engineering and Role AI Scientist and integrated data and information

Leave a reply

I’d like to offer some advice on transitioning skills and knowledge skills and knowledge who worked hard to retainto include some of the new AI and NLM developments it’s actually less impactful, and better than you may think

Well, I dressed up in a little bit for now let’s talk about prompt engineers You most likely have currenb SME’s or expert on your current data and requirements

First, do you notice I used the term AI scientist Instead of data scientist , A data scientist Currently is actually a AI model scientist and they will help you apply. We are concerned here with how a lot of folks have opinions heuristic no not necessarily fact-based already we are going to suggest some techniques, and provide some mentoring to explore this important factor in AI is proper training we specialize in providing techniques and mentoring in separate information which is Formulated and an opinion and facts or Data, which, cannot change

There is a series of steps involved in preparing for the use of Data inAI and Chat in the form of LLM models. This is not much different and you may have most of the information already gathered in order to properly design the requirements for your model we would collect the phone.it is important to realize the steps are critical, for you have confidence in your models putput which will be your result of integrating, your Word documents, your Presentations,, spreadsheets, and, of course your actual data.

We wiKeaton, Billy, and modeling of information words versus modeling and requirements for data preparation . There is a difference that is extremely important and in line with what you’ve been doing.I know that Data preparation is not glamorous, but in my 20+ years you’ll get nowhere without proper data preparation you can’t AI it you can’t impute it you need to discuss requirements with people and write them down and then execute it The AI will make the legwork, faster. but in the end you’ll have to review it otherwiseit otherwise you may end up needlessly retracing your steps based on Improper preparation I know that Data preparation is not glamorous, but in my 20+ years you’ll get nowhere without proper data preparation you can AI it you can’t imputed you need to discuss requirements with people and write them down and then execute a I will do it. Faster time is the legwork, but in the end you’ll have to review with Stuck you may end up needlessly retracing your steps based on.I know that Data preparation is not glamorous, but in my 20+ years you’ll get nowhere without proper data preparation you can AI it you can’t imputed you need to discuss requirements with people and write them down and then execute a I will do it. Faster time is the legwork, but in the end you’ll have to review with Stuck you may end up needlessly retracing your steps based on. improper preparation. This can be at Floydd by phone, the proper steps.

1. Word document

2. Presentations

3. Spreadsheets

4.Data reports

5. Data quality report for AI preparation

6.Internet

7.Other Sources (Network,Internet or Local)

We have suggested tools/techniques/open source.and suggestions for each of these. Don’t let that bother you, however, is important with today’s capabilities in AI integrate your words your thoughts, your abstraction, and your actual data together in order for you. They’re trustworthy results from your AI.

We will be providing a separate post on each of these and then finally how they come together our point is that the what you’ve been doing to understand and form requires for tradition BI can be reutilized and extend it for AI

Data Governance and AI.

Leave a reply

With a little guidance, you can actually chat with the information you’ve got in caliber, or any data governance tool and integrate that with your PI Data Warehouse and the Data Marts

This would be pPossible by leveraging new capabilities, not necessarily new vendors no doubt new vendor features are on the horizon but this ability to chat with your data governance information is here now if you have already implemented the quality or MDM, or even Data Governance early adoption and prototyping of AI is possible today. We can enable this very quickly and easily by leveraging, our current, capabilities, and knowledge and tools. Who is B) in vomiting and Luigi current Lenckee or Microsoft copilot capabilities along with LLM and provide you the ability to create your own. LLM privately and insecure, basically, LM is what provides chat capability but this time with your personal data in addition, we have exceptional data quality capabilities, which can also be enhanced for you This capability will be taking traditional BI, and data governance, as well as data quality and MDM to explosive to new heights Finally, we have a decade of experience. This is merely extension of the Information Value Chain methodologies. Which we can gladly help you take advantage of

Evolving Data Into Information through Lineage

Leave a reply


Information lineage through data DNA

 

Ira Warren Whiteside         

 

ORIGINS
COMMON SENSE

We in IT  have complicated and diluted the concept and  process of analyzing data and business metrics  incredibly in the last few decades. We seem to be focusing on the word data.         

“There is a subtle difference between data and information.”

Information vs data

There is a subtle difference between data and information. Data are the facts or details from which information is derived. Individual pieces of data are rarely useful alone. For data to become information, data needs to be put into context.

Examples of Data and Information

The history of temperature readings all over the world for the past 100 years is data.

If this data is organized and analyzed to find that global temperature is rising, then that is information.

The number of visitors to a website by country is an example of data.

Finding out that traffic from the U.S. is increasing while that from Australia is decreasing is meaningful information.

Often data is required to back up a claim or conclusion (information) derived or deduced from it.

For example, before a drug is approved by the FDA, the manufacturer must conduct clinical trials and present a lot of data to demonstrate that the drug is safe.

“Misleading” Data

Because data needs to be interpreted and analyzed, it is quite possible — indeed, very probable — that it will be interpreted incorrectly. When this leads to erroneous conclusions, it is said that the data are misleading. Often this is the result of incomplete data or a lack of context.

For example, your investment in a mutual fund may be up by 5% and you may conclude that the fund managers are doing a great job. However, this could be misleading if the major stock market indices are up by 12%. In this case, the fund has underperformed the market significantly.

 

Comparison chart

Synthesis: the combining of the constituent elements of separate material or abstract entities into a single or unified entity ( opposed to analysis, ) the separating of any material or abstract entity into its constituent elements.

Synthesis         

 Data into Information is dominant in terms of data movement and replication, in essence data logistics.

 

Lineage is the key.

 

 And with the simple action of linking data file metadata names to a businesses glossary  or terms, Will result in deeply insightful and informative business insight and analysis.

“Analysis the separating of any material or abstract entity into its constituent elements”

 In order for a business manager for analysis you need to be able to start the analysis at a understandable business terminology.

 And then provide the manager with the ability to decompose or break apart the result.

They are three essential set of capabilities and associated techniquestechniques for analysis and lineage.

 

  1. Data profiling and domain analysis as well as fuzzy matching components available on my blog https://irawarrenwhiteside.com/2014/04/13/creating-a-metadata-mart-via-tsql/
  2. Meta-data driven creation of a meta-data mart through code generation techniques, implemented.

 

 

Underlining each of these capabilities is a set of refined, developed and proven code says for accomplishing these basic fundamental task.

 

One case study

 I have been in this business over 45 years and I’d like to offer one example of the power of the concept of a meta-data mart and lineage as it regards to business insight.

 

A lineage, information and data story for BCBS

I was called on Thursday and told to attend a meeting on Friday between our companies leadership and the new Chief Analytics Officer. He was prototypical of the new IT a “new school” IT Director.

 I had been  introduced via LinkedIn to this director a week earlier as he had followed one of my blogs on metadata marts and lineage.

 

After a brief introduction, our leadership began to speak and the director immediately held up his hand he said “Please don’t say anything right now the profiling you provided me is at the kindergarten level and you are dishonest”

 

 The project was a 20 week $900,000 effort and we were in week 10.

 The company has desired to do a proof of concept and better understand the use of the informatics a tool  DQ as well as direction for a data governance program.

 

 To date what had been accomplished was in a cumulation of hours of effort in billing that has not resulted in any tangible deliverable.

 

The project had focused on the implementation and functionally of the popular vendor tool, canned data profiling results and not providing information to the business.

 

 The director commented on my blog post and asked if we could achieve that at his company, I of course said yes.

 Immediately I proposed we use the methodology that would allow us to focus on a  tops down process of understanding critical business metrics and a bottoms up process of linking data to business terms.

 My basic premise was that unless your deliverable from a data quality project can provide you business insight from the top down it is of little value. In essence you’ll spend $900,000 to tell  a business executive they have dirty data. At which point he will say to you “so what’s new”.

 The next step was to use the business terminology glossary that existed in informatica  metadata manager and map  those terms to source data columns and source systems, not an extremely difficult exercise. However this is the critical step in providing a business manager the understanding and context of data statistics.

 The next step, was the crucial step in which we made a slight modification to the IDQ tool and allowed the storing of the profiling results into a meta-data mart and the association of a business dimension from the business glossary the reporting statistics.

 We were able to populate my predefined metadata mart dimensional model by using the tool the company and already purchased.

 Lastly by using a dimensional model we were able to allow the business to apply their current reporting tool.

 

 Upon realizing the issues they faced in their business metrics, they accelerated the data governance program and canceled the data lake until a future date.

 

 

 

Now for the results. 

 Within six weeks we provided an executive dashboard based on a meta-data mart that allowed the business to reassess their plans involving governance and a data lake.

 Here are some of the results of their ability to analyze their basic data statistics but mapped to their business terminology.

 

  •  6000 in properly form SS cents
  • 35,000 dependence of subscribers over 35 years old
  • Thousands of charges to PPO plans out of the counties they were restricted to.
  •  There were mysterious double counts in patient eligibility counts, managers were now able to drill into those accounts by source system and find that a simple Syncsort utility had been used improperly and duplicated records.

 

                    

                               

                                                 

 

 

 

 

 

 

         

 

                                        

 

         

 

 

 

 

 

 

 

 

J

Merry Christmas Data Classification, Feature Engineering , Data Governance. ‘How to’ do it and some code take a look

Leave a reply


I was heavily involved in business intelligence, data warehousing and data governance as of several years ago and recently have had many chaotic personal challenges, upon returning to professional practice I have discovered things have not changed that much in 10 yearsagovernance The methodologies and approaches are still relatively consistent however the tools and techniques have changed and In my opinion not for the better, without focusing on specific tools I’ve observed that the core to data or MDM is enabling and providing a capability for classifying data into business categories or nomenclature.. and it has really not improved.

  • This basic traditional approach has not changed, in essence man AI model predicst a Metric and is wholly based on the integrity of its features or Dimensions.



Therefore I decided, to update some of the techniques and code patterns, I’ve used in the past regarding the information value chain and or record linkage , and we are going to make the results available with associated business and code examples initially with SQL Server and data bricks plus python

My good friend, Jordan Martz of DataMartz fame has greatly contrinuted to this old mans BigData enlightenment as well as Craig Campbell in updating some of the basic classification capabilities required and critical for data governance. If you would like a more detailed version of the source as well as the test data, please send me an email at iwhiteside@msn.com. Stay tuned for more update and soon we will add Neural Network capability for additional automation of “Governance Type” automated classification and confidence monitoring.

Before we focus on functionality let’s focus on methodology


Initially understand key metrics to be measured/KPI‘s their formulas and of course teh businesse’s expectation of their calculations

Immediately gather file sources and complete profiling as specified in my original article found here

Implementing the processes in my meta-data mart article would provide numerous statistics regarding integers or float field however there are some special considerations for text fields or smart codes

Before beginning classification you would employ similarity matching or fuzzy matching as described here



As I said I posted the code for this process on SQL Server Central 10 years ago here is s Python Version.

databricks-logo Roll You Own – Python Jaro_Winkler(Python)

databricks-logoroll You Own – Python Jaro_Winkler(Python)
Import Notebook
Step 1a – import pandas
import pandas

Step 2 – Import Libraries

libraries from pyspark.sql.functions import input_file_name

from pyspark.sql.types import *
import datetime, time, re, os, pandas

ML Libraires

from pyspark.ml.feature import RegexTokenizer, StopWordsRemover, NGram, HashingTF, IDF, Word2Vec, Normalizer, Imputer, VectorAssembler
from pyspark.ml import Pipeline

import mlflow

from mlflow.tracking import MLFlowClient

from sklearn.cluster import KMeans
import numpy as np
Step 3 – Test JaroWinkler

JaroWinkler(‘TRAC’,’TRACE’)
Out[5]: 0.933333
Step 4a =Implement JaroWinkler(Fuzzy Matching)

%python

def JaroWinkler(str1_in, str2_in):

if(str1_in is None or str2_in is None):
return 0

tr=0
common=0
jaro_value=0
len_str1=len(str1_in)
len_str2=len(str2_in)
column_names=[‘FID’,’FStatus’]

df_temp_table1 = pandas.DataFrame(columns=column_names)
df_temp_table2 = pandas.DataFrame(columns=column_names)

#clean_string(str1_in)
#clean_string(str2_in)

if len_str1 > len_str2:
swap_len=len_str2
len_str2=len_str1
len_str1=swap_len
swap_str=str1_in
str1_in=str2_in
str2_in=swap_str

max_len=len_str2

iCounter=1

while(iCounter <= len_str1):
df=pandas.DataFrame([[iCounter,0]], columns=column_names)
df_temp_table1=pandas.concat([df_temp_table1,df], ignore_index=True)
iCounter=iCounter+1

iCounter=1

while (iCounter <= len_str2):
df=pandas.DataFrame([[iCounter,0]], columns=column_names)
df_temp_table2=pandas.concat([df_temp_table2,df], ignore_index=True)
iCounter=iCounter+1

iCounter=1
m=round((max_len/2)-1,0)
i=1

while(i <= len_str1):
a1=str1_in[i-1]

if m >= i:
  f=1
  z=i+m 
else:
  f=i-m
  z=i+m 
if z > max_len:
  z=max_len
while (f <= z):
  a2=str2_in[int(f-1)]
  if(a2==a1 and df_temp_table2.loc[f-1].at['FStatus']==0):
    common = common + 1
    df_temp_table1.at[i-1,'FStatus']=1
    df_temp_table2.at[f-1,'FStatus']=1 
    break
  f=f+1
i=i+1

i=1
z=1
while(i <= len_str1): v1Status=df_temp_table1.loc[i-1].at[‘FStatus’] if(v1Status==1): while(z <= len_str2): v2Status=df_temp_table2.loc[z-1].at[‘FStatus’] if(v2Status==1): a1=str1_in[i-1] a2=str2_in[z-1] z=z+1 if(a1 != a2 ): tr = tr+0.5 break break i=i+1 wcd = 1.0/3.0 wrd = 1.0/3.0 wtr = 1.0/3.0 if (common != 0): jaro_value = (wcd * common)/ len_str1 + (wrd * common) / len_str2 + (wtr * (common – tr)) / common return round(jaro_value,6) Step 4b – Register JaroWinkler spark.udf.register(“JaroWinkler”, JaroWinkler) Out[6]:
Step8a – Bridge vs Master vs AssociativeALL

%sql
DROP TABLE IF EXISTS NameAssociative;
CREATE TABLE NameAssociative;

SELECT
Name
,NameInput

,sha2(replace ( NameLookput,’%[^a-Z0-9, ]%’,’ ‘) , 256) as NameLookupCeaned ,a.NameLookupKey
,sha2(replace( NameInput,’%[^a-Z0-9, ]%’,’ ‘) , 256) as NameInput,b.NameInputKey
,JaroWinkler(a.NameLookup, b.NameInput) MatchScor
,RANK() OVER (Partition by a.DetailedBUMaster ORDER BY JaroWinkler(a.NameLookupCleande, b.NameInputCleaned) DESC) NameLookup,b.NameLookupKey
FROM NameInput as a
CROSS JOIN NameLookup as b

Explaining the Path to Data Governance from the Ground Up!

Leave a reply

We will explore a methodology for understanding and implementing Data Governance that relies on an ground up approach described by Michael Belcher from Gartner as using ETL and Data Warehouse techniques to build a metadata mart, in order to “boot strap” a Data Governance” effort.

We will discuss the difference between the communication required for data governance and the engineering approach to implementing the “precision” required for MDM, via Data Profiling.

With Data Governance can apply the age old management adage “You get what you inspect, not what you expect” Readers Digest.

We will describe how to implement a data quality dashboard in Excel and how it supports a Data Governance effort in terms of inspection.

Future videos will explore how to build metadata repository as well as the TSQL required to load and update the repository and the data model as well as column and table relationship analysis using the Domain profiling results.

Metadata Mart the Road to Data Governance

2 Replies

 or Guerilla Data Governance

Implementing a Metadata Mart the Road to Data Governance best viewed in PRESENTATION Mode, there is animation.

iradg

This presentation has narrative, play in presentation mode with sound on.

https://onedrive.live.com/redir?resid=E7059817CA5789D0!10028&authkey=!AE6eWl-gzh2mq_M&ithint=file%2cpptx

Self Service Semantic BI (Business Intelligence) Concept

Leave a reply

metadatamarttripleWe want to build an Enterprise Analytical capability by integrating the concepts for building a Metadata Mart with the facilities for the Semantic Web

 

    • Metadata Mart Source
      • (Metadata Mart as is) Source Profiling(Column, Domain & Relationship)
    • +
      • (Metadata Mart Plus Vocabulary(Metadata Vocabulary)) Stored as Triples(subject-predicate-object) (SSIS Text Mining)
    • +
      • (Metadata Mart Plus)Create Metadata Vocabulary following RDFa applied to Metadata Mart Triple(SSIS Text Mining+ Fuzzy (SPARGL maybe))
    • +
    • Bridge to RDFa – JSON-LD via Schema.org
      • Master data Vocabulary with lineage (Metadata Vocabulary + Master Vocabulary) mapped to  MetaContent Statements)) based on person.schema.org
      • Creates link to legacy data in data warehouse
      • +RDFa applied to web pages
      • +JSON-LD applied to
      • + any Triples from any source
  • Semantic Self Service BI
    • Metadata Mart Source + Bridge to RDFa

 

 

 

 

I have spent some time in this for quite a while now and I believe there is a quite a bit of merit in approaching the collection of domain data and column profile data, in regards to the meta-data mart, and organize them in a triple’s fashion

 

The basis for JSON-LD and  RDFa is the collection of data as a triple. Delving into said deeper

 

I believe with the proper mapping for the object reference and deriving of the appropriate predicates in the collection of the value we could gain some of the same benefits as well as bringing the web data being “collected, there by linking to source data.

Consider the following excerpt regarding Vocabularies derived from MetaContent via Metadata Structure

“Metadata structures[edit]

Metadata (metacontent), or more correctly, the vocabularies used to assemble metadata (metacontent) statements, are typically structured according to a standardized concept using a well-defined metadata scheme, including: metadata standards and metadata models. Tools such as controlled vocabularies, taxonomies, thesauri, data dictionaries, and metadata registries can be used to apply further standardization to the metadata. Structural metadata commonality is also of paramount importance in data model development and in database design.

Metadata syntax[edit]

Metadata (metacontent) syntax refers to the rules created to structure the fields or elements of metadata (metacontent).[11] A single metadata scheme may be expressed in a number of different markup or programming languages, each of which requires a different syntax. For example, Dublin Core may be expressed in plain text, HTML, XML, and RDF.[12]

A common example of (guide) metacontent is the bibliographic classification, the subject, the Dewey Decimal class number. There is always an implied statement in any “classification” of some object. To classify an object as, for example, Dewey class number 514 (Topology) (i.e. books having the number 514 on their spine) the implied statement is: “<book><subject heading><514>. This is a subject-predicate-object triple, or more importantly, a class-attribute-value triple. The first two elements of the triple (class, attribute) are pieces of some structural metadata having a defined semantic. The third element is a value, preferably from some controlled vocabulary, some reference (master) data. The combination of the metadata and master data elements results in a statement which is a metacontent statement i.e. “metacontent = metadata + master data”. All these elements can be thought of as “vocabulary”. Both metadata and master data are vocabularies which can be assembled into metacontent statements. “

 

The MetadataMart serve as the source for both metadata vocabulary and MDM for the Master Data Vocabulary.

For the Master Data Vocabulary consider schema.org which defines most of the schemas we need. Consider the following schema.org Persons Properties of Objects and Predicates:

 

Thing > Person

A person (alive, dead, undead, or fictional).

Property Expected Type Description
Properties from Person
additionalName Text An additional name for a Person, can be used for a middle name.
address PostalAddress Physical address of the item.

The key is to link source data in the Enterprise via a Business Vocabulary from MDM to the Source Data Metadata Vocabulary from a Metadata Mart to conform the triples collected internally and externally.

 

In essence information from the web applications can be integrated with the dimensional metadata mart, MDM Model and existing Data Warehouses providing lineage for selected raw data from web to Enterprise conformed Dimensions that have gone thru Data Quality processes.

 

Please let me know your thoughts.

Guerilla MDM via Microsoft MDS: Force Model Validation or TSQL Script to force set Validation Status ID ‘s

1 Reply

Using Metadata and code generation to programmatically set Validation Status ID’s  for all leaf members in an MDS Entity.

Primarily this script relies on the MDS mdm.udpMemberValidationStatusUpdate procedure for a single member. Unfortunately I could not get the stored proc for multiple members(mdm.udpMembersValidationStatusUpdate) to work, so I created a script to rely on the single member version.( mdm.udpMemberValidationStatusUpdate). I could not resolve the “operand type clash” error.

The script requires the model name and entity name, to generate a TSQL statement to update each member id to the designated Validation status.

IF OBJECT_ID('tempdb..#MemberIdList') IS NOT NULL
DROP TABLE #MemberIdList

DECLARE @ModelName nVarchar(50) = ‘Supplier’
DECLARE @Model_id int
DECLARE @Version_ID int
DECLARE @Entity_ID int
DECLARE @Entity_Name nVarchar(50) = ‘Supplier’
DECLARE @Entity_Table nVarchar(50)
DECLARE @sql nVarchar(500)
DECLARE @sqlExec nVarchar(500) = ‘EXEC mdm.udpMemberValidationStatusUpdate ‘
DECLARE @ValidationStatus_ID int = 4
— Found the following information intable [mdm].[tblList]
–ListCode ListName Seq ListOption
–lstValidationStatus ValidationStatus 0 New, Awaiting Validation
–lstValidationStatus ValidationStatus 1 Validating
–lstValidationStatus ValidationStatus 4 Validation Failed
–lstValidationStatus ValidationStatus 3 Validation Succeeded
–lstValidationStatus ValidationStatus 2 Awaiting Revalidation
–lstValidationStatus ValidationStatus 5 Awaiting Dependent Member Revalidation
–MemberType_ID = 1 (Leaf Member)
DECLARE @MemberType_ID int = 1
–Get Version id for Model
SET @Version_ID = (SELECT MAX(ID)
FROM mdm.viw_SYSTEM_SCHEMA_VERSION
WHERE Model_Name = @ModelName)
print @Version_ID
–Get Model IDfor Model
SET @Model_ID = (SELECT Model_ID
FROM mdm.viw_SYSTEM_SCHEMA_VERSION
WHERE Model_Name = @ModelName)
print @Model_ID
–Get Entity ID for specific Entity
SET @Entity_ID =
(SELECT [ID]
FROM [mdm].[tblEntity]
where [Model_ID] = @Model_ID
and [Name]= @Entity_Name)

–Get Entity Table Namefor specific Entity

SET @Entity_Table =
(SELECT[EntityTable]
FROM [mdm].[tblEntity]
where [Model_ID] = @Model_ID
and [Name]= @Entity_Name)
print ‘Processing Following Model ‘ + convert(varchar,@ModelName)
print ‘Model ID = ‘ + convert(varchar,@Model_id)
print ‘Version ID = ‘ + convert(varchar,@Version_ID)
print ‘Entity = ID ‘ + convert(varchar,@Entity_ID)
print ‘Entity Name = ‘ + convert(varchar,@Entity_Name)
print ‘Entity Table Name ‘ + convert(varchar,@Entity_Table)
print ‘Validation Status ID being set to ‘ + convert(varchar,@ValidationStatus_ID) + ‘ for all members in ‘+ convert(varchar,@Entity_Name)

–Create local temp table to hold member ids to update
CREATE TABLE #MemberIdList
( id int,
Processed int)
–Generate SQL to populaet temp table
set @sql = N’INSERT INTO #MemberIdList select id, 0 from mdm.’ +convert(varchar,@Entity_Table)

–Generate SQL to display stored proc update
EXECUTE sp_executesql @sql

–Create Tabe Variable to hold SQL Commands and prepare for execute
Declare @Id int
DECLARE @MemberSQL TABLE
(
ID int,
mdssql nvarchar(500),
Processed int
)
While (Select Count(*) From #MemberIdList Where Processed = 0) > 0
Begin
Select Top 1 @Id = ID From #MemberIdList Where Processed = 0

set @sql = N’ mdm.udpMemberValidationStatusUpdate ‘ +convert(varchar,@Version_ID) +’,’ +convert(varchar, @Entity_ID) +’,’ +convert(varchar,@ID) +’,’ +convert(varchar, @MemberType_ID) +’,’ + convert(varchar,@ValidationStatus_ID)

print @sql

INSERT INTO @MemberSQL
Select @id , @sql, 0

Update #MemberIdList Set Processed = 1 Where ID = @Id

End

Declare @sqlsyntax nVarchar(500)

While (Select Count(*) From @MemberSQL Where Processed = 0) > 0
Begin
Select Top 1 @id = id From @MemberSQL Where Processed = 0
Select Top 1 @sqlsyntax = mdssql From @MemberSQL Where Processed = 0

print @sqlsyntax
— comment this line to not execute update Validation Status ID
EXECUTE sp_executesql @sqlsyntax

Update @MemberSQL Set Processed = 1 Where ID = @Id
End

Declare @Entity_TableSQL nVarchar(500)
set @Entity_TableSQL = ‘SELECT * FROM [mdm].’ + @Entity_Table
print @Entity_TableSQL
exec sp_executesql @Entity_TableSQL
Here are several links I referenced:

The code for forcing the Validation of the Model is here. Jeremey Kashel’s Blog

Microsoft SQL Server 2012 Master Data Services 2/E Tyler Graham

Table Variables in TSQL

I am sure this can be improved, please contact me with questions or suggestions.

-Ira Warren Whiteside

BI / Enterprise Data Warehouse using Agile/SCRUM modded via Extreme Scoping

Leave a reply

I have recently had the opportunity to help in clarifying the issues and techniques in adapting an Agile/SCRUM “in progress” ENTERPRISE Data Warehouse project via the Extreme Scoping methodology from Larissa Moss. Thankfully I have been working with an excellent Scrum Master Andrew Ruffalo.

Please see the articles below one does an excellent job in providing an explanation of how you can modifyAgile, using a well defined and proven approach of Extreme Scoping as well as an article on Information Architecture, the last one is a presentation I gave with Aaron Zornes on the use of Metadata Marts.

Also here is a brief treatment of the these areas as I see it:

First we will have several layers of Data/Information Models spanning the Enterprise from left to right. they are as follows:

Staging – SourceOriented

Enterprise – Target or Business Oriented in our case this this will probably be based on some type of Common “Canonical” Model (ie Accord?)

Data Mart – A “Pure Dimensional” model consisting of Conformed Dimensions and Purposed Facts for your Business Model and mapped back to the EDW And the “Canonical” model.

Analytical – This will also map back to the “canonical” model and will be physically represented in an OLAP cube.

MetaData Mart spanning all models and supporting complete linage and mappings between all layers, including the “canonical model”

If we agree on these layers then it is relatively straight forward to follow an Agile\Extreme approach , creating User Stories (Business Questions) within Epics consisting of a few Metrics , Dimensions and Hierarchies and in essence implementing them in the Staging and EDW, Data Mart and Cubes.

The technique is to fully population the Staging Model, “sparsely populate” the EDW(Common Model) , which will be abstracted from a “canonical” model,incrementally design and load the Data Mart(Data Mart, Cube). there will be consist “refactoring” of the entire Application.

The overwhelming priority has to be supporting the “Information Value Chain”, and the Business Capabilities Matrix as the driver.

Information Value Chain: Objectives => Business Question => Metric => Dimension => Characteristic

Business Capabilities Matrix: Epics,Stories across the top Metrics and Dimensions down the side, leading to intersection of Business Capabilities and Dimension Model

.
I believe we need to stay true to these principles, obviously you need to incorporate your teams experiences and knowledge in aligning there approaches and plans with the approved Solution Architecture and Information Architecture as well as following proven and verifiable “best practice” several are listed below..

This will enable advanced predictive analytics, financial modeling as well as data mining by incorporating data lineage as well as data quality, delivered and verified by the Business Product Owner and Sponsors very quickly.

In the coming weeks we will be detailing the specifics techniques we used and diving into what worked and what didn’t.

Articles / Webinars

Beware of Scrum Fanatics On DW/BI Projects – Larissa Moss

Beware of Scrum Fanatics On DW/BI Projects

Information Architecture: Do Not Model the Staplers – David Loffredo
http://msdn.microsoft.com/en-us/library/cc168600.aspx

Creating a Metadata Mart the Road to Data Governance. -Aaron Zorness / Ira Warren Whiteside
http://blog.melissadata.com/data-quality-authority/2010/11/free-webinar-creating-a-metadata-repository-metadata-mart-on-the-road-to-data-governance.html

Creating a Metadata Mart incorporating Data Governance , Data Profiling , Code Generation via SQL, SSIS – Ira Warren Whiteside
http://www.sqlpass.org/Events/ctl/ViewEvent/mid/521.aspx?ID=656