Data is an asset of IT?

I’ve read an interesting article on Information Management about Data is an asset.  In this article Jim Ericson states that data is an asset and there is more than just an intrinsic value of data. It can be made explicit. Jim talks about taking the net value of the projects that uses the data and this is the gain you can get of using data.  In my opinion, these thoughts are very IT driven.
In Erik’s assumption he talks about projects done by IT and summarizing the net values of these projects and there you are, you have the value of data. Is this really the case? What about business opportunities what  data can achieve? What if a report is badly designed in case of usability or gives a poor insight?  What if a BI developer created an awesome analytical insight what assists the business in a very helpful manner? This kind of advantages is very implicit. You can’t calculate adding up the net values of projects!

Off course, I've been in the situation when a business user said to me : “Just give me the data and I'll build something in Excel” and yes, I think that there should be ways to enable this. I’ve seen some great work with excel worksheets in one of my former projects. Those were people who know what data means and how to handle this. There's another side to the matter (and that’s when IT should be in sight) that sometimes these solutions were developed over and over again (with some adjustments) for different departments. When standardization, skills or scalability is needed, IT comes into play. These excel implementations can be used for prototyping, but in the end you need to standardize the general parts.

This discussion reminds me of another discussion about power users, casual users and Managed Self service BI. Power users, like business analysts, wants data to explore regarding correlations, insights and opportunities. For casual users standard reports are quite enough to do their job.  So give the power users freedom to develop great visualizations but don’t let them free in doing their excel/access habit. Look over their shoulder and standardize the general parts.

Another thing that is involved here is the ‘one single version of the truth’ principle. Bringing all of the disparate data into a data warehouse, has created the IT backlog in last decade. This generated the Self Service BI movement. Making IT responsible for delivering all of the information would create an IT backlog.

So should data/information be an asset or should data/information be an asset of IT  (from data to information to knowledge to wisdom)? It has some nuances. IT (!) depends - On:

• Maturity of the IT department.
• On the BI/DWH knowledge of the IT department.
• Depends on the strategy of the organisation (and maturity). Do you want to enhance a smarter organisation? What responsibilities should IT have and what should be the responsebility of  the business user (power and casual).
• Kind of data.
• Depends on the ease of understanding of data structure.
• Depends on the data quality. If the data quality is poor, you need some specialized personnel to clean up the mess.
• Data governance. Can you trust your data?
• Master Data Management (sort of a quality check of data).
• Security. Do you want to show all the data to your employees.

So there is no silver bullet for this. There are too much interdependencies between all these subjects. My advice in these cases would be: write a BI strategy document and take all these subjects into account. Define a far sight and create a road map to get there.

Hennie

Quipu : Discovering the metatables.

In my former posts about Quipu i already blogged about the installation process, generated datavault model and one post about installing Quipu meta database on SQL Server. Today i want you to show something about the metatables of Quipu. In the diagrams below only the interesting tables are shown. So don’t blame me that i didn’t show all the tables;-)

Import schema
When i imported the schema SalesLT of the AdventureWorksLT2008R2 database in Quipu ‘something’ happened in the following tables:

Specially the tables with numbers are interesting:

• qp_schema
• qp_index
• qp_identifier
• qp_column
• qp_table
• qp_relationship
• qp_reference

In these tables the  metadata is stored about the datamodel in the source database. All seems very logical but the relation ship between qp_relationship and qp_reference seems an 1:1 but i’m not sure (yet).

Generate stage
The next step what i’ve done is generating the staging database with the option ‘Generate staging’ and when i executed this option the following changes were discovered in the tables op de metadata database:

Some changes appeared in the already inventoried tables in the Quipu metadata tables. Two extra tables are filled with data:

• qp_sql_step
• qp_mapping

In table 'qp_sql_step' i’ve found the generated DDL statements. The 'gp_mapping' table is connected with the columns table and the table 'qp_sql_step'. I’m not sure what role is of this table. The table 'qp_sql_step' contains the generated SQL statements per table. With my limited knowledge (yet), i would think that it should be connected to the table 'qp_table'. May be i find out in the future. Another thing i remarked is that the ETL code is stored in the 'qp_sql_step table' and not the DDL code.

Generating Datavault
The last step i’ve executed is generating the datavault model. When i generated the datavault, the following model is a representation of changed number of records in the tables.

There are no new tables used for filling new records, so this seems it concerning the dynamic metadata of generating the data vault. The following things catched my eyes:

• The SQL code is generated from templates stored in the table 'qp_sql_template'.
• There is no sql code in 'qp_sql_step' table for generating ETL/DDL for the datavault in contrast with the staging scheme. May be the code of the staging area is a mistake? Logically it would be best to generate the SQL code right from the metatables and the templates.

So that’s all folks..

Gr,
Hennie


----------------------------------

Update after comments of The Los Tilos.

The above diagrams have more relationships. In the diagram below relationships are shown better.

Quipu : Installing 1.0.4 with SQL Server

Qosqo developed a new version of Quipu and today i installed it on my VM. I decided to install the metadata in SQL Server so i could look in the tables what was going on in Quipu. Below you can read my findings.

In order to install Quipu in SQL Server you need to set a environmentvariable: QUIPU_CONFIG and give it a proper value: A path to file where connectionstring resides. Accidentally (or on purpose) Qosqo already defined an example for an connection on the following location :.\webapps\quipu-config.properties. So the environmentvariable should be pointed to this location.

In this location there is a file in which the following information is stored (use wordpad):

dataSource.dbCreate=update
dataSource.driverClassName=net.sourceforge.jtds.jdbc.Driver
dataSource.url=jdbc:jtds:sqlserver://localhost:1433/qpdb
dataSource.username=sa
dataSource.password=sa

When all is well, the metatables are created in the SQL Server database qpdb.

Gr
Hennie

Managed Self Service BI

For a while it was on my mind to write somehing about Managed Self Service BI. After presenting this subject, it became clear to me that this subject is a good candidate for a post on my blog. Managed Self Service BI, as i define it, is a framework of doing things right on the fields of organisation, processes and technology. I would like to define organisation as what can an organisation do to organize themself in a manner that it is efficient and effective (when employing Self Service BI), technology is everything technical enabling Self Service BI and processes is the oil between the organisation and technology. Processes (procedures) will enhance the possibilities of technology and organisations. These three fields can be aligned or they are not aligned. Perhaps this is comparable with the CMM levels where CMM level 0 is total chaos and CMM level 5 is that everything is under control. Let's take a look at the diagram below:

 

Organisation

The organisational part is about how to organize your organisation, team or business to aid Self Service BI in the best optimal way. At first you need to have a clear vision on what your goals are regarding Self Service BI. What do want to achieve? Define a roadmap to achieve to meet that goal. It's better to take multiple steps than one giant step.

Self Service BI for everybody in your organisation is a tricky thing. What are the risks if everyone 'does' Self Service BI? If there are risks perhaps it's better to make a distinction between power users and casual users. It's very important defining who are casual users and who will be the power users. Power users are the users who have the responsibility and freedom developing BI solutions.

The next best thing is training your employees about Self Service BI. Understanding the principles about Self Service BI, best practices, technologies to be used, etc are important issues here.

Technology

On the technology side care should be taken for a robust BI infrastructure. The BI infrastructure should support Self Service BI as good as possible. Complex queries or queries from unexperienced users can pull down your BI infratructure. Off course select BI tools that support Self Service BI in the best possible way. To often suppliers claim that they have tooling with Self Service BI support but at the end the tooling is not intuitive and users still need support from IT.

Process

The process focus area  is about organizing the procedures and processes around technology, organisation and people. When people building Self Service BI solutions it's difficult to determine whether the report is trustworthy. So need to make sure that users can rely on datasources that is reliable. I think that you have certify data in a way that users can trust this data. Users can compare figures, counts or whatever of their reports with the certified reports, datasources or report parts (usable in mashups). 

Also think about creating a sandbox where users can play, try, explore with data and the tooling.

Greetz,

Hennie

SSIS : WITH RESULT SETS in Denali

In a blog of Derek Dieter i've read something about executing stored procedures with result sets. As it seems now to me, the WITH RESULT SETS clause of the EXEC statement makes it possible to specify explicitly the result set of a stored procedure (and not change it with casting). At this moment the following websites seems to have some interesting information about this option:

• Microsoft SQL Server forum.
• James Rowland-Jones.
• MSDN.

The EXEC statement is enhanced with the following clauses:

• RESULT SETS UNDEFINED 
• RESULT SETS NONE
• RESULT SETS ( <result_sets_definition> [,...n ] )

And the <result_set_definition> does have the following possibilities specifying the result:

• Datatype.
• Length.
• NOT NULL| NULL 
• Collation.

Although it's possible to do some casting of datatypes of fields, it's only possible when the data fits into this sort of a cast. Below you can see some examples i experimented with.

First let's create a stored procedure:

CREATE PROCEDURE spGetPerson
AS
BEGIN
    SELECT FirstName, MiddleName, LastName FROM Person.Person
END 

and execute it :

EXEC spGetPerson
WITH RESULT SETS
(
    (
        NameFirst NVarchar(50),
        NameMiddle nVarchar(50),
        NameLast NVarchar(50)
    )
);


Execute this will give an error:

EXEC spGetPerson
WITH RESULT SETS NONE
GO

Msg 11535, Level 16, State 1, Procedure spGetPerson, Line 4
EXECUTE statement failed because its WITH RESULT SETS clause specified 0 result set(s), and the statement tried to send more result sets than this.


And executing this is ok :


EXEC spGetPerson
WITH RESULT SETS UNDEFINED
GO

You can also use this for executing this :

EXECUTE sp_executesql  N'SELECT FirstName, MiddleName, LastName FROM Person.Person'
WITH RESULT SETS
(
    (
        NameFirst NVarchar(50),
        NameMiddle nVarchar(50) NOT NULL,
        NameLast NVarchar(50)
    )
);


And this will give an error:

EXECUTE sp_executesql  N'SELECT FirstName, MiddleName, LastName FROM Person.Person'
WITH RESULT SETS
(
    (
        NameFirst NVarchar(50),
        NameLast NVarchar(50)
    )
);

Msg 11537, Level 16, State 1, Line 1
EXECUTE statement failed because its WITH RESULT SETS clause specified 2 column(s) for result set number 1, but the statement sent 3 column(s) at run time.


And  the following script shows all the possibilities you have with RESULT SETS (except multiple Result sets):

EXECUTE sp_executesql  N'SELECT FirstName, MiddleName, LastName FROM Person.Person'
WITH RESULT SETS
(
    (
        NameFirst NVarchar(50) NOT NULL,
        NameMiddle nVarchar(50) NULL,
        NameLast NVarchar(50) COLLATE Latin1_General_100_CI_AS
    )
);


And the final example shows multiple result sets:

--Suppose you have two resultsets:
CREATE PROC Production.ProductList @ProdName nvarchar(50)
AS
-- First result set
SELECT ProductID, Name, ListPrice
    FROM Production.Product
    WHERE Name LIKE @ProdName;
-- Second result set
SELECT Name, COUNT(S.ProductID) AS NumberOfOrders
    FROM Production.Product AS P
    JOIN Sales.SalesOrderDetail AS S
        ON P.ProductID  = S.ProductID
    WHERE Name LIKE @ProdName
    GROUP BY Name;
GO

-- Execute the procedure
EXEC Production.ProductList '%tire%'
WITH RESULT SETS
(
    (ProductID int,   -- first result set definition starts here
    Name Name,
    ListPrice money)
    ,                 -- comma separates result set definitions
    (Name Name,       -- second result set definition starts here
    NumberOfOrders int)
);


Conclusion:
It sounded as a promising feature to me but i'm a bit disappointed. There are only four options available and i realize that it has some potential. But selecting some columns is not possible, and many more things.

Greetz,
Hennie