Data Warehouse - Technology Agnostic

Very honored to be referenced by MSSQLTIPS

I am very honored that one of my blogs is being referenced by MSSQLTips as exam material help. I started this blog a while ago because I got frustrated with forgetting some cool stuff I've done before, and I am very glad that it not only helped me, but help some in the community. Microsoft developer community has been so great, I'm happy to do my part. http://www.mssqltips.com/sq... My blog being ......

Posted On Thursday, March 20, 2014 10:46 AM | Comments (0)

Very honored to be referenced by MSSQLTIPS

I am very honored that one of my blogs is being referenced by MSSQLTips as exam material help. I started this blog a while ago because I got frustrated with forgetting some cool stuff I've done before, and I am very glad that it not only helped me, but help some in the community. Microsoft developer community has been so great, I'm happy to do my part. http://www.mssqltips.com/sq... My blog being ......

Posted On Thursday, March 20, 2014 10:28 AM | Comments (0)

Real-time report VS Application

It is very often that we report developers get requests about "real-time" reports. I don't know about you, but I cringe every time I hear about this. For some people, "real-time" report means I have a report that executes a query in the database to get result back. But when you think about it, if you answer "yes" to any of the following questions, you don't really have a real-time report: Is your dataset cached on the report server? Is the query result cached on the database server? Does your source ......

Posted On Thursday, March 20, 2014 10:18 AM | Comments (0)

Different Mind Set Between Data Warehouse and System Integration

I love doing database design with people. I've done my fair share of database modeling on my own; those are the easy projects, sure, but with no feedback. However, working with people on database never fails to help me look at things from different perspective.A very simple thing like whether a NOT NULL table column should have default values get very different reaction from a BI guy and a System Integration guy. Let me put some of the priorities out from different projects I've been in to help understand ......

Posted On Friday, March 1, 2013 2:54 PM | Comments (0)

SSAS processing error: Client unable to establish connection; 08001; Encryption not supported on the client.; 08001

After getting the cube to successfully deploy and process on Friday, I was baffled on Monday that the newly added dimension caused the cube processing to break. I then followed the first instinct, discarded all my changes to reverted back to the version on Friday, and had no luck. The error message (attached below) did not help as I was looking for some kind of SQL service error. After examining the windows server log and the SQL server log, I just couldn't see anything wrong with it.After swearing ......

Posted On Thursday, June 21, 2012 4:09 PM | Comments (23)

Using Recursive SQL and XML trick to PIVOT(OK, concat) a "Document Folder Structure Relationship" table, works like MySQL GROUP_CONCAT

I'm in the process of building out a Data Warehouse and encountered this issue along the way.In the environment, there is a table that stores all the folders with the individual level. For example, if a document is created here:{App Path}\Level 1\Level 2\Level 3\{document}, then the DocumentFolder table would look like this:IDID_ParentFolderName1... 121Level 232Level 3To my understanding, the table was built so that:Each proposal can have multiple documents stored at various locationsDifferent ......

Posted On Wednesday, April 4, 2012 5:29 PM | Comments (0)

Date Dimension: use date in YYYYMMDD format if your granular level is a single date

It still surprises me how many people set up the DateDimID as Identity(1, 1).If you use YYYYMMDD format, then on some reports, you can even display the date without navigating to the Date dimension to show label. In the same deal, you can use YYYYMM for a monthly date dimension, and YYYY for a yearly date dimension.[Update 2012-10-16]I now realize that what I said before was a clunky way to build dimension (hey I didn't say I was the expert. :D) And using only that argument going up against Kimball's ......

Posted On Wednesday, March 14, 2012 11:39 AM | Comments (0)

Data warehouse design principle, a very good post to always steer me back in the right direction

Whenever I start doubting my design of Data Mart or Cube, I always come back to this article and read it again.  It helps me keep my design simple.

http://dwbi1.wordpress.com/2010/06/17/one-or-two-dimensions/

Great post!

Related post:
http://dwbi1.wordpress.com/2011/02/26/denormalising-a-fact-table/

Posted On Wednesday, March 14, 2012 11:34 AM | Comments (0)

Database Hardware CPU Licensing vs Performance and Fast Track Data Warehouse Architecture

This is just one of those cool post which are not written as often as it should be. Hats off to Glenn Berry for stating it so clearly for all BI / database developers.http://sqlserver... read some Microsoft paper on "Fast Track Data Warehouse Architecture", and did not appreciate the benefit at the time. What a great way for developers to start with someone already figured out the licensing + cost + performance. There ......

Posted On Thursday, March 8, 2012 4:05 PM | Comments (0)

Database Model Diagram

Some very useful diagrams here.  It provides a basis point.  I see that it already does not really fit all project requirements, but this is a really nice starting point, and a good check on business entity relationship.

http://www.databaseanswers.org/data_models/index.htm

Posted On Wednesday, March 7, 2012 12:13 PM | Comments (0)

Change Data Capture to get a paricular set of change within a time span

The "fn_cdc_map_time_to_lsn" is especially useful DECLARE @begin_time datetime, @end_time datetime, @from_lsn binary(10), @to_lsn binary(10) SET @begin_time = '11/10/2010 10:00 AM' SET @end_time = '11/10/2010 8:00 PM' SET @from_lsn = sys.fn_cdc_map_time_to_lsn(... greater than or equal', @begin_time); SET @to_lsn = sys.fn_cdc_map_time_to_lsn(... less than or equal', @end_time); SELECT _OU.unit_natural_id AS Order_Id , CONVERT(DATETIME, LEFT(CONVERT(VARCHAR(80), _OR.return_body.query(' ......

Posted On Wednesday, November 10, 2010 5:38 PM | Comments (0)

Copyright © Kevin Shyr

Design by Bartosz Brzezinski

Design by Phil Haack Based On A Design By Bartosz Brzezinski