Informing you about the Dan linstedt session in the Netherlands

Hi,

This week Dan Linstedt visited the Netherlands for a session about Datavault. The title of the seminar was about best practices  in datavault. There were sessions about implementation about Tele2 and Vektis for showing how Logica implemented the datavault at some customers.

Below i bulleted the most significant subjects of the evening (to my viewpoint):

• There's 'no version of the truth' but a 'single version of the fact'. When you think about it, it makes sense to me. What he meant is that once there is a agreement about some KPI's and the system is implemented, some other managers, which didn't participate in the requirementsgroup, could have another viewpoint on a calculation of a KPI. Or perhaps a new manager want to measure KPI's in another way. Could be a point. But i see also some difficulties here:
• When the calculation of KPI changes to much, it's difficult to compare figures over a longer time. How do you handle this?
• I blogged about Self Service BI and i indicated that there will companywide KPI's but there will be also specific manager KPI's. How do you deal with this? Do you change the companywide KPI when a new manager enters the town? No, as in my former post said, you have to put a organisational unit in place: Governance committee. This group needs to decide about this stuff.
• Dan showed the following graph of a classic datawarehouse system. The problem with this kind of architecture that it's not auditable. The data is changed when the data enters the datawarehouse. I've done projects where the ETL developer got the blame because he didn't implemented the KPI in a proper manner. Who is the owner of the data? The business! Why should a ETL developer change the data? Hmm interesting viewpoint, but you could also say that the business didn't test the solution rightly. I know that testing is always a problem because of timeconstraints or less involvement of the business, etc.

•  Dan showed another picture, as you can see below. In this picture the rules are propagated later in the architecture. This way the system is more auditable, you can change the KPI and recalculate it over you history. A next step is to implement a business vault in this model (not shown) which is a view over the datavault. This could be any datamodel that fits the business (e.g. Datavault modelling, starmodelling, etc).

• The physical model will be less important in the future. This point is not quite clear to me, yet. I think it has to do with cloudcomputing. You define a model and you don't care about the physical implementation of it. 
• He also phrased in the same part that columnar MPP will be more important in the future. Agree with that.
• Data as a Service is growing and will take firm ground. Where the data is coming from will be less important. It could be that the master data will come from datasuppliers and that the datawarehouse in a company will be factgenerators. The dimensions are master data.
• Operational datawarehousing is also taking firm ground.
• Dan spoke also about dynamic datawarehouse. This can also be seen in the light of the agility developments. The datawarehouse will be more organic, it will change according to the developments in the business or communities. How? Good question. But i think that datavault is a organic like modelling technique and anchor modelling is even more flexible. This will enhance the dynamic datawarehousing! 

Regards,
Hennie

SSAS : IgnoreUnrelatedDimensions

Currently working with analysis and MDX and i ran into the property IgnoreUnrelateddimensions. This property controls whether the All member is substituted in place of any specified members from unrelated dimensions.  
The MDX query is something like this :  
SELECT [Measures].[Extended Amount] ON 0,
[Dim Reseller].[Reseller Name].MEMBERS ON 1
FROM [Adventure Works DW]   

And when i executed this at first it appeared to me that something is wrong:

But when i changed the property IgnoreUnrelatedDimensions the values changed to null and that's more intuitive because there's no relation between the measure and the dimension.

And the results are:

Small post about a small handy property ;-) Greetz,Hennie

CustomRollup

In this article about SSAS, i want to show you an implementation of a custom rollup. In this example I’ll show you a customrollup with a parent-child relationship. Most of time a aggregation of the data is done with a sum, max, min or the other standard aggregation. 

An example is depreciation. I borrowed this from the book “Microsoft SQL ServerAnalysis Services 2008 with MDX”. Depreciation is about “mapping an assets expense over time to benefits gained through use of those assets”, meaning that the value of assets decrease over time.You can deprecate in different manners, exponential, lineair, etc.

In the foolowing example we’ll use an account dimension from the adventureworks database, that indicates the types of accounts of your company such as asset, liability, income and expenditure. Together with a amount we could rollup the information . In case of parent-child hierarchy, analysis services allows you to perform a custom rollup using a feature called unary operators. Unary operators allow a specification of basic aggregation functions, add, subtract, etc. 

If you look into the account dimension, available in the adventureWorks dimension and you incorporates this into an Analysis project, you’ll see something like this:

In the properties of an attribute you'll see a property called CustomRollupColumn. This property needs to be set to the relational column that has the MDX expression  for the customrollupformula. The MDX expression gets evaluated on the cells when the values corressponding members of the hierarchy are being retrieved in the query. 

The sample AdventureWorks database provides a column with custom rollups for the account dimension : operator.

So you can see how the hierarchy of account attributes needs to be rolled up by the operator column. Right click on the ParentAccountkey and specify the levelnames for the parent-child hierarchy like below:

Set the IsAggregatble to false and click on the '...' caption of the CustomRollupcolumn in the properties window. Below you can see the result of custom rollup in the Finance fact:

So this is an example of the customrollup property.

Greetz,

Hennie

Join elimination

While reading an article about anchormodeling, I was triggered by the sentence: “An anchor model requires join elimination techniques in your RDBMS”. Join eliminiation? The writer about this article pointed me at a blog from the optimizer Oracle development team. Interesting! 


In that blog they talked about 'table eliminitation' (alternately called "join elimination"), which removes redundant tables from a query. A table is redundant if its columns are only referenced to in join predicates, and it is guaranteed that those joins neither filter nor expand the resulting rows.  On this blog you can find some examples.

At first the following script is shown on the Oracle blog :

create table jobs
( job_id NUMBER PRIMARY KEY,
    job_title VARCHAR2(35) NOT NULL,
    min_salary NUMBER,
    max_salary NUMBER );


create table departments
( department_id NUMBER PRIMARY KEY,
  department_name VARCHAR2(50) );


create table employees
( employee_id NUMBER PRIMARY KEY,
  employee_name VARCHAR2(50),  department_id NUMBER REFERENCES departments(department_id),
  job_id NUMBER REFERENCES jobs(job_id) );

This create statement shows me that there are references, meaning a foreign key.

An simple SQL query example, which i copied from the Oracle site:


select e.employee_id, e.employee_name 
from employees e 
where not exists 
(select 1 from jobs j where j.job_id = e.job_id);


Resulting in this execution plan (Not sure whether Oracle calls this execution plan)

-------------------------------------------
 Id   Operation             Name      
-------------------------------------------
   0  SELECT STATEMENT                
*  1   TABLE ACCESS FULL    EMPLOYEES 
-------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("E"."JOB_ID" IS NULL)

A full table scan is done on the employees table. The jobs table isn't scanned and it shouldn't, because there is no need to scan the job table.

select e.employee_name
from employees e, departments d
where e.department_id = d.department_id; 

The Oracle optimizer will generate this plan for the query:

-------------------------------------------
 Id   Operation             Name      
-------------------------------------------
   0  SELECT STATEMENT                
*  1   TABLE ACCESS FULL    EMPLOYEES 
-------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
 1 - filter("E"."DEPARTMENT_ID" IS NOT NULL)

So this is aqueryplan from the Oracle and you can see here that the department table isn't  scanned. Strange? How is Oracle determining the innerjoin? Well because of the REFERENCE relation ship, which you can see in the CREATE TABLE script.

I also tried some other queries. I created two views :

• vwINNERJOIN
• vwOUTERJOIN

Here are the scripts:

DROP VIEW vwINNERJOIN
GO

CREATE VIEW vwINNERJOIN AS
SELECT P.Color, PS.Name as ProductSubcategory, PC.Name as Productcategory
FROM Production.Product P
INNER JOIN Production.ProductSubcategory PS ON P.ProductSubcategoryID = PS.ProductSubcategoryID
INNER JOIN Production.ProductCategory PC ON PS.ProductCategoryID = PC.ProductCategoryID
GO

---
DROP VIEW vwLEFTOUTERJOIN
GO

CREATE VIEW vwLEFTOUTERJOIN AS
SELECT P.Color, PS.Name as ProductSubcategory, PC.Name as Productcategory
FROM Production.Product P
LEFT OUTER JOIN Production.ProductSubcategory PS ON P.ProductSubcategoryID = PS.ProductSubcategoryID
LEFT OUTER JOIN Production.ProductCategory PC ON PS.ProductCategoryID = PC.ProductCategoryID

And  i  executed the two queries:

SELECT Color

FROM vwINNERJOIN

SELECT Color

FROM vwLEFTOUTERJOIN

and this resulted in the following execution plan:

So the example is about selecting one field from a view with multiple joining. And the execution plan shows that er is only one table scan in case of a left outer join and one more for the inner join.

So the conclusion is that SQL server doesn't adapt 'Join elimination' in case of a innerjoin but does it in case of the left join. This is because of the cardinality of the query. In case of the Inner join there should be Foreign key record present because it will be excluded from the result set. Therefore a scan is needed. So it seems that SQL Server applies Join elimination.

Regards,
Hennie

SQLPass meeting 21 september 2010

I joined a session at the dutch chapter of SQLpass and it was about gamechangers. Due to recent developments (SSD, fusion IO) in hardware and databasetechnology (NoSQL, columnar databases) innovations are going pretty fast. So what does this mean for the work of a DBA, SQL Developer, datawarehouse architect or anyone who is working with databases? The session had two main parts : a presentation video of DR. David DeWitt (Microsoft Data and Storage) from PASS Summit  2009 and the next part was a discussion about what does the recent developments mean for the current and future job activities of the (SQL Server) database specialist.

The first part of this night was a videopresentation of Dr David deWitt at PASS Summit 2009 and was about the developments in hardware over the past 30 years and he analyzed that the current disks are 10000x larger than 30 years ago, memory is 1000 x larger, CPUs are 2000x faster and CPU Batches are 1000x faster. As he explained further, CPU has become 1000x faster but IO has only become 10x faster and SSD seems to be the only real hope for getting things done faster. Also he made a comparison between the transferrate of data 30 years ago and nowadays and the outcome was that the disks are 150 x has become relatively slower than 30 years ago!! So improvements in CPU and memory increased more than improvements on disks.

After that a lot of technical stuff with L1, L2 and memory caching he explained that the column store is, in theory, 7 times faster than row stored based databases. But there’s a tradeoff, updating is slower than row based databases. Conclusion was that column based databases reduces the amount of disk I/O required  to execute a query and improves CPU performance by reducing memory stalls.  Best practice is not the use a column store database for operational purposes. He believed that due to hardwaretrends more specialization will happen in the future.

After this a discussion started about SSD’s, fusion IO’s, IOPS, virtual file stats, use SSD for tempdb’s, listing pending I/O’s. Also there was a talk about integrated memory of memory and storage. This is an interesting development because I learned from the first books about computers that the Von Neuman architecture always is based on a cpu, an in- and external memory. This paradigm will change in the future. No more harddisks? What would this mean? Are things going so fast that there’s less need for work done by ICT professionals, like architecture development, DBA and performance tuning, etc? Is that the future? Or will volumes of data grow that fast that there will always a need for ICT professionals? Time will learn…

Another viewpoint is that 10 year ago the world was much easier. You had a database and VB6 application and that was it. But nowadays we have all kind of new features in products, complex environments, more data, more (different) user needs. As Dr David deWitt states more specialized products will be on the market in the future and therefore more specialization of ICT-professionals is inevitably.

So the title of the evening was 'Gamechangers'. What is a gamechanger? I think that you should define what a gamechanger is. As we look at the past what are typical gamechangers: the IBM desktop, internet, mobile devices are typical gamechangers. Also you could define gamechangers as a revolution, a turnaround. In this discussion, i didn't hear a gamechanger. The stuff i heard were more a evolutions: the column based database is specialized database (evolution) and the SSD disk is an evolution of the external memory. Things (talked about at that SQL Pass evening) don't change that much, only are going faster or are more specialized.

Greetz,

Hennie