Training Notes on 2nd Day of SQL Analysis Sever

Realtime Warehouse - Do we really have such thing ?You can't analyse the data when it is not consistent for some time

Find the difference between - ER Model Vs Dimensional Model

Dimensional Model rules
1) Almost Dimensions Tables are mostly refered 1 to many to Fact Table
2) Summerizable numerical values are Measures
3) Dimension is an indipendent entity of a specific concept. For example Jan, Feb, Mar, etc are Time dimension and India, USA, Russia, etc are location dimension blah blah
4) Every thing that you see in world are grouped
5) From one Dimension one or more hierarchy is created
6) Think for only ANALYSIS, but not for information that is hidden and unwanted. The prime target of this tool is only to understand the progress and analyse the business but not for getting the details of the transacion
7) Remember that, how better you can give the user the Drill down capability

Customers - Dimension

ID CustName City State Country MaritalStatus Gender Tel eMail

How many hirarchies you can create out of the above individual labels?

H1) GenderwiseMaritalstatusTelnumber - Wrong Hierarchy
H2) H1+eMail - Wrong Hierarchy
H3) StateWiseGenderMaritalStatus

Note: Hierarchies are created only the grouping possible labels, where as Tel and eMail can't be grouped, using them in hierarchy is a bad design

Types of Hierachies
1) Balanced - All leafs will have equal number of Members / LevelsRule 1 - All leave memebers should be at the same levelRule 2 - The parent member don't have the values, they are procured from the childs

2) UnBalanced - Not necessarily you will have the equal number of levels or members

3) Ragged - Ragged are more similar like Balanced but just missing some parents
Never Make the OLTP Primary keys same PKs of Application System
Dimension tables are almost smallFact Tables are generally huuuuuuuuuuuuge

Schemas
1) Snow-Flake - Bad Design
2) Star

Thing to remember: If report is taking long time to conceptualise, it is inotherwords conveying that the database design is bad. So, if the report is taking less time to conceptualise on the DB what you have, is a good design as, visualised the report while designing
"Change the granularity is beyond the scope"

Dimension are not required to create repeatedlyRole Playing Dimension - Similar to the Table Alias

Measures are of 3 types
1) Additives
2) Semi Additive
3) Non Additive

Slowly Changing Dimension

-----------------------
If you have something to share in person on this post, pl drop me a mail at dskcheck@gmail.com with the title in the subject.

Comments

Post a Comment

Popular posts from this blog

Network Intrusion Detection using Supervised ML technique

Declaimer This is my initial version of the synopsis for PhD studies. There are plenty of things that i have to complete before i prepare the final version. Thus, this would be the alpha version. Due to the length of the blog post, will not be posting the entire draft. 1.Title of the thesis Network Intrusion Detection using Supervised Machine Learning Technique with Feature Selection 2.Introduction Intrusion Detection System, IDS, is a development which improve the network security and defending the information of the association. The IDS encourage the network chairman to recognize any malicious action on the network and alarms the executive to get the information made sure about by taking the appropriate activities against those attacks. An intrusion alludes to any unauthorized access or malicious usage of data assets. An intruder or an attacker is a true element that attempts to discover a way to increase unauthorized access to data, causes hurt or take part in different
Read more

Common mistakes by Interviewer

Now-a-days am conducting few interviews for our organization. While sitting in the other side of the table, I've visualized why most of the interviews fail with me. But when conducting these interviews, most of the candidates are attending with self confidence that they are suitable for the mentioned role and attend without preparation. When people do some real preparation, they tend to fail due to lack of awareness at the interviewer towards interviewing techniques. Having failed in many interviews, I've learned that the interviews fail because of the following... Reflection (or) Mirroring: The interviewer tends to see self within the candidate. The interviewer starts to compare self with the candidate and evaluate. This evaluation is totally personal. The yard stick for measuring the candidature is not generalized, but influenced.   Template (or) Checklist: The interviewer has a template of a questionnaire. The interviewer measures the candidate with respect to the list of st
Read more

Keep the system active, to avoid the auto lock

Most of the corporate offices will have a policy for screen locking. While working with a long running applications at background, like the automation scripts for testing the applications, it would be difficult to maintain the unlock state of the screen. Thus, we found a technique to keep the system active with a cheating technique. Below is the powershall scripts that keeps the system active ' I am clicking on NUMLOCK for every 5 seconds to keep the system awake Set objNetwork = CreateObject("Wscript.Network")'to get user name numOfHours=inputbox("Hi " & objNetwork.UserName & ", How many hours you want me to be awake? :->") 'time variable conatins the time in hours Set WshShell = WScript.CreateObject("WScript.Shell") 'Creating an object for clicking keys from keyboard numOfMilliSecs=numOfHours*3600*1000 'converting hours to milli seconds While numOfMilliSecs>0 'runs until tym becomes zero WshShell.Send
Read more