DevOps series : Working with teams in a data warehouse : ownership (part II)

Introduction

This blogpost is a successor of the blogpost "DevOps series : Working with teams in a data warehouse (part I)". In order to control the changes of the objects in a large data warehouse environment with multiple teams, one of the first steps you have to take is to define ownership. Who is the owner of that object? Because if all teams are responsible for the objects, no one is responsible for the objects. Sometimes there is some implicit knowledge who is responsible, and perhaps consultations happens between teams, but it will quickly grow out of hand when the number of teams grow. Therefore, to get control over de objects, teams need clear understanding and take ownership and responsibility for the objects for which they feel and are responsible.

Vertical and horizontal teams 

Teams can be organized in different ways; teams can be responsible for a vertical solution, for instance, from source to dashboard or teams are responsible for a horizontal layer, eg. the data warehouse layer. A mix is also possible: sometimes teams build a data warehouse for it's own information products (eg. dashboards) and later on, other teams build dashboards on top of these data objects.

I've made a distinction between the different parts (I call them modules) of a data warehouse : sources (delivery), Data ware house, and information products. Examples of a source (delivery) are extraction code, data lake, file copy code and staging objects. For your data ware house, think about raw vault, business vault and datamarts. There is a lot of discussion (in my current role)  about raw vault, because hub and links are more business driven but the sats from the different sources are more source driven. We discussed some scenarios like a source vault solution and an integration layer for integrating the BK's and the links. Although there is a lot of resistance from Dan Linstedt and others as well, for source vaults but you can make your deployments independent from other teams with this approach. And in order to compensate the source vault approach an integration layer can be adopted. Other options are possible as well. Next, the information products, these are more end user delivery oriented and think about components like SQL views for the cube or report, cubes (Tabular model) and dashboards (PowerBI). With this approach, teams are more independent and can deliver their code much faster to production.

Now, the preferred team pattern in my opinion is team pattern number 1. This team pattern is responsible to process the data from source to dashboard.The team is able to deliver data from start to end. It is not (or less) dependable on the work of other teams. This is also in line of a DevOps Team. The team is able to deliver stand-alone products to the business.

Although, the other patterns are less preferable, these can happen too. For instance, team pattern 3 happens sometimes when a management dashboard is needed and retrieves the data from all kind of different areas of a company, eg finance, HR, Sales, etc. Because of this reason it's not feasable to create a team that is responsible from source to end. So this creates the need for a team that is dependent on the work of other teams.

Team pattern 2 is an example of a horizontal team and this pattern occurs a lot in companies. It's very common to organize teams in cost efficient ways in order to utilize resources as efficient as possible. Although it seems logical to do this, but this team pattern is not organized around delivering products to the customer but around people. This approach will lead to waiting time. No added value is delivered to the end user until information products are build on top of them (by other teams) and this causes handover moments and inefficiencies.

Final thoughts

This is the second blogpost about working in large data warehouse environments with multiple teams. 

Hennie

DAT208x : Introduction to Python for Data Science

Introduction

I'm participating in the Microsoft Professional Program AI by Micrsosoft. I've already done the programs Data science and Big data. These are also part of the Microsoft Professional Programs. I've experienced them as an easy to follow instruction based courses. There are more of these programs available that are interesting for data enthusiasts. Think about Internet of Things and Data analysis.

The great thing about these programs is that these programs consists of high quality instructor led courses, broken in easy to digest videos, exercises, labs and quizzes on the Edx site. So every minute spare time you have you can follow a couple of videos.

The program is broken in the following courses :

• Introduction to Artificial Intelligence (AI)
• Introduction to Python for Data Science
• Essential Mathematics for Artificial Intelligence
• Ethics and Law in Data and Analytics
• Data Science Essentials
• Build Machine Learning Models
• Build Deep Learning Models
• Build Reinforcement Learning Models
• Develop Applied AI Solutions
• Microsoft Professional Capstone : Artificial Intelligence

DAT208x : Introduction to Python for Data Science

This course is an introduction of Python in combination with data science. There are other Python courses available but they do not always focus on data science. This course it is. The course is a collaboration between Edx and DataCamp and I have to say that the interaction between the two sites works great.

The course is divided in the following sections:

• Python Basics
• List - A Data Structure
• Functions and Packages
• Numpy
• Plotting with Matplotlib
• Control Flow and Pandas
• Final Lab
• Final Exam

Final thoughts

The whole scripting is executing in a controlled environment of DataCamp. They did a great job building an integrated learning environment. Every section has one or more labs and they are graded in Edx.

The Final Lab is lot of work and covers more than the material in the sections and in the videos. This took me quite some time finding out how and what. Google is your friend here. The Final Exam contains 50 questions and must be finished within 4 hours. You have limited time per question.

Hennie

Getting filtered data from a tabular with Excel

Introduction

Building a PowerPivot model on filtered data of a table from a SQL Database is fairly simple. In the table properties you can change the table view to query view and change the query. I haven't found a similar way when using a tabular with Excel (realtime connection).

The only option I have found so far is using the CommandText in the connection properties, but that changes my look and feel of browsing the cube. The retrieved data from the tabular is just like one plain table. This blogpost will describe the way of adjusting the .odc file in Excel and getting the data as a plain table.

The goal of this blogpost is to show how to filter the factdata based on a dimension value (DimCustomer).

The Datamodel

I've created a simple model in SQL Server and imported it into the Tabular project. It is modeled as follows.

One Facttable with a key to the DimCustomer table and a measure Amount. I added a few records.

Some simple EVALUATES

In SSMS, I experimented with some EVALUATES to get some data from the Tabular. Below the result of this exercise.

--Getting the data from a table
EVALUATE Fact

--A simple  query (and join between tables)
EVALUATE
SUMMARIZE (Fact
,DimCustomer[Name]
,"TotalAmount", SUM(Fact[Amount])
)

--A simple query and a calculated measure is used
EVALUATE
SUMMARIZE (Fact
,DimCustomer[Name]
,"TotalAmount", Fact[TotalAmount]
)

--A calculated measure and a Filter
EVALUATE
FILTER(
 SUMMARIZE (Fact
 ,DimCustomer[Name]
 ,"TotalAmount", Fact[TotalAmount]
 ),
 DimCustomer[Name] = "Hennie"
)

Adjusting the .odc file

From the experiments in SSMS i took one of the DAX Evaluates and inserted that in the CommandText tag in the odc file of Excel.

This is the result in the Connection properties in the Excel file. The CommandType combobox is changed to Default and in the CommandText box a DAX EVALUATE expression is presented.

The result in Excel

And below you can see the fields in the FieldList and as you can see the Look and Feel has changed of the FieldList. 

Conclusion

This blogpost is about how to filter the data in Excel from a Tabular Model.


Best regards,

Hennie

SSAS : Processing a Tabular Model

Introduction

This blogpost is about processing a tabular model in Analysis Services in SQL Server 2014. At first glance, the number of processing options is a bit overwhelming but when you have a certain understanding about the options you get the idea behind it. Although, I've gathered this information for SQL Server 2014, most of it is applicable for other versions as well.

I've read a couple of blogposts about processing a tabular model and most of this blogpost is based on the following blogs:

• Processing tabular models 101
• Wiseowl
• SQLBI

So all the credits goes the people who wrote these blogs.

Stages of loading data

There are two stages of loading data in a tabular model. First, The xVelocity engine takes each column in each table and builds a dictionary from it. 

Secondly, data recalculation takes place, meaning that the engine populates any calculated columns, hierarchies, relationships and indexes for the database.

Processing options

As said before, SSAS Tabular model comes with a couple of processing options that can be used differently depending on the object you want to process. 

Process Add

With the processing option Process Add it is possible to implement incremental loading of the cube. By defining the rows that are new to the cube, incremental loading of the cube is possible. As Marco Russo is writing in his blog, when executing the Process Add option, the SSAS engine will create a new partition and this is merged with the existing partition.

An effect of the Process Add is that automatically recalculates the affected objects.

Process Clear

Process Clear drops all the data in a database, table, or partition. Is there more to say;-)

Process Data

Process Data loads data into a partition or a table. It reads the data and it builds dictionaries, but doesn't process any calculated columns, indexes, relationships or hierarchies. Therefore it's required to perform a Process Recalc after all tables' dictionaries have been updated using Process Data.

Process Default

Process Default loads data into unprocessed partitions or tables. Any affected calculated columns, relationships, user hierarchies, or internal engine structures (except table dictionaries) are recalculated. It recalculates the affected objects (and only the affected objects).

Process Defrag

Process Defrag optimizes the table dictionary (an internal engine structure) for a given table or for all tables in the database. This operation removes all dictionary entries that no longer exist in the data set and rebuilds the partition data based on the new dictionaries.

If you often add or remove partitions from a table, or if you often reprocess individual partitions in a table, you should Process Defrag regularly but carefully. 

Process Defrag may be an expensive operation for a large table.

If you Process Data on a table level, it is never necessary to do a Process Defrag

Run Process Recalc after running Process Defrag.

Process Full

Process Full loads data into all selected partitions or tables. Any affected calculated columns, relationships, user hierarchies, or internal engine structures (except table dictionaries) are recalculated. It recalculates the affected objects. Process Full runs a Process Data on the table or partition, followed by a Process Recalc.

Process Recalc

For all tables in the database, recalculates calculated columns,  Process Recalc rebuilds relationships. rebuilds user hierarchies, and rebuilds other internal engine structures. Table dictionaries are not affected.

Process Recalc is always the last thing you do, and you can batch up as many processing operations as you want before issuing that last Process Recalc.

Keep in mind that you can only issue Process Recalc at the database level. This means that after a Process Data operation on a dimension, you may be stuck recalculating a large fact table when it was not impacted by the initial processing operation.

Processing scenarios

I've read a couple of interesting options for loading the tabular model. 

1) Full load

With Process Full you can perform a full load of the database, partition or a table. It will drop all of the data and then it will process the object. This can become handy for an initial loading of the cube or in case of a structural change of an object in the cube or the cube itself. In that case, you need to redeploy the cube and perform a Process Full of the cube.

2) Incremental processing

Two options:

• 1) Create a new partition and then use Process Data to populate it. This new partition can then be merged with an existing partition if you like.
• 2) Use Process Add to add data to the existing partition. If used make sure that you don’t add duplicate rows. 

          Two options:

• Change the partition definition to the query that returns the set of rows you want returned by the incremental processing operation. 
• If your partition definition is based on a view, make sure that the view on the original data source now returns just the set of rows you want returned by the incremental processing operation.

3) Semi-incremental processing

Yet another option is to create a partition for a year or a month in a table and process that with a Process Data. This will limit the data loading significantly but it 's simple but effective.

No matter how you do incremental processing, you must Process Recalc on the database afterwards before moving on with your life. If you do this enough, you’ll want to Process Defrag too.

Greetz,

Hennie

BI and Enterprise Architecture (EA)

Introduction
In some linkedin groups I was reading about that BI should be a Enterprise Architecture (EA) implementation for organizations and i was curious what is meant by that. What is an Enterprise Architecture implementation and how can BI be a part of it? So in order to understand more about the implementation of a Enterprise Architecture i decided to study TOGAF. Currently studying for the Foundation certification (level 1) and it's a huge book (sigh!).  It takes a lot of time to understand the methodology, the phases, terminology, keywords, etc. But interesting though!

TOGAF is well-known Enterprise Architecture Framework and can be seen as a tool for assisting in acceptance, production, use and maintanance of enterprise architecture. In this blogpost i'm trying to translate the TOGAF framework to a plan to implement BI as an Enterprise Architecture.

Enterprise Architecture according to TOGAF
So what is an Enterprise Architecture? What is an Enterprise? What is a Architecture?

Definition Enterprise: "Collections of organizations that has a common set of goals".  Organizations are getting smaller and are functioning in neworkorganizations. Think about an airliner that farms out the luggage handling or ticket selling. Therefore in case of network organizations you should look beyond the boundaries of an organization.

Definition Architecture (ISO) : "The fundamental organization of a system, embodied in its components, their relationships to each other and the environment and the principles governing its design and evolution".

Defintion Enterprise Architecture (SearchIO): "An enterprise architecture (EA) is a conceptual blueprint that defines the structure and operation of an organization. The intent of an enterprise architecture is to determine how an organization can most effectively achieve its current and future objectives."

TOGAF makes also a distinction between an architecture and a solution. The distinction is that a architecture is like a blueprint of the organization and a solution is like an instance of the architecture. You could compare these between a logical model and physical model (in my opinion).

There are four points of views regarding TOGAF :

• Business perspective. The business perspective defines the processes and standards by which the business operates on a day-to-day basis.
• Application perspective. The application perspective defines the interactions among the processes and standards used by the organization.
• Information perspective.The information perspective defines and classifies the raw data (such as document files, databases, images, presentations, and spreadsheets) that the organization requires in order to efficiently operate.
• Technology perspective. The technology perspective defines the hardware, operating systems, programming, and networking solutions used by the organization.

In an Enterprise Architecture framework all of these viewpoints are worked out: a baseline is created, a target architecture, gap analysis, a roadmap, review and an implementation is executed.

Translate TOGAF to BI (or BI to TOGAF)
Generally spoken, there is low interest in BI projects regarding working from within an Enterprise Architecture viewpoint. In  most of my projects done the solution is targeted at a certain area of interest: for instance finance. Another issue is that BI projects are under time pressure meaning that BI projects are driven by need for a solution for more or less a specific problem and clients wants it fast.

I don't think about BI as an holistic Enterprise Architecture approach but more as part of the Enterprise Architecture. The ideal situation would be that BI is part of a Enterprise Architecture Program where BI should be adopted at all kind of different levels of the enterprise internally and externally  (think about the network organisations).

Where is BI positioned in TOGAF? You need to understand the business perspective (baseline and target) and you need to understand how BI could aid the target business perspective in such a way that it can benefit from BI. And BI is much about data and there the data perspective is also specific area of interest in case of BI.

Conclusion
The BI Enterprise Architecture can be positioned on the business perspective and on the data pespective of the TOGAF Framework. The BI Enterprise Architect must understand both perspectives and should be aware and understand the overall Enterprise Architecture picture (baseline and target in order to make a flexible, easy to replace building and easy to maintain (BI?) architecture and solution building blocks (ABB and SBB).

Make sure business intelligence is a core part of your Enterprise Architecture Planning!

Greetz,
Hennie

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

What is data?

I follow now and then some discussions about data. Some say: “well data is like water; it flows through your organization and people will use the data as they like”. It’s a nice analogy but it’s too simple to say that, because data can be of all kinds of forms: simple, complex, float, text, etc. So data is flowing through your organization as little, big, roundy or roughly pieces. Little pieces for the easy businessprocesses and complex data for complex analytical questions. So, data can have all kind of forms and characteristics. Let’s describe some more characteristics of data:

• Data is a representation of the world around us, at some moment in time. Some data will be accurate (temperature readings) and some won’t (human entry systems). So, especially, don’t expect that data is non - erroneous representation of the world around us. It will allways has flaws. This is what we call data quality. How accurate describes data the world around us?

• Data has a life cycle. Data is created, updated, expired, renewed, destroyed, backupped, etc. In a datawarehouse we  mostly keep all the data for years in a detailed level. Why? Well, because we can, we never know if we gonna need it, we don’t know how we are gone use it (lowest grain), because diskspace is cheap, etc etc. All reasons to keep the data in your systems.

Data about fashion sales in a fashionshop of the last day can be very important. Fashion sales from a couple of months ago about a certain day will be less important. May be data should aggregate more when it's get older. Below you can see a couple of data life cycle curves positioned against importance.

• Data can be very valuable at a moment in time. This depends on the success of the actions of an organization, department or person. Data is only valuable when it’s used. If it’s not used it doesn’t represent a value.

• Data can be aggregated to a higher level or it could be kept at a low level (grain).

• Data describes something and it can describe things : master data and it can describe events: transactional data.

• Data will be less valuable when the source is difficult to determine or unclear. Also when the data is ‘scrambled’ during ETL processes users tend to discuss this and could feel unsecure whether the data is correct.

• Data could be objective or it could be subjective. Objective when it’s trivial, like the address of a customer but it will be less objective when the data is processed for business decisions. Mostly data quality is mentioned with operational data like an address of a customer but when the data is aggregated to a higher level the ‘data quality’ of this indicator is also important. In this case the business rules should be judged for correction adaptation.

Gr,
Hennie