Microsoft SQL Server's SQL Server Integration Service (SSIS) may be used to perform a broad variety of data migration activities. As a tool for extracting and transforming data, SSIS may be used for a wide range of tasks including data cleansing and aggregation as well as merge and fusion. SQL Server Integration Services are explained in detail in this blog, both at a basic and intermediate level.
DATA: data is composed of observable and recordable facts that are often found in operational for transactions systems.
OLTP: OLTP is nothing but an observation of online transaction processing. The system is an applicable application that modifies data the instance it receives and has a large number of concurrent users.
OLAP: OLAP is an abbreviation of online analytical processing this system is an application that collects manager processes and presents multidimensional data for analysis and management purpose.
DATA MINING: data mining is the process of analyzing data from different perspectives and summarizing it into useful information.
BI: BI is the leveraging of BW to help make business decisions and recommendations. Information and data rules engines are leveraged here to help make these decisions along with statistical analysis tools and data mining tools.
DWH DEFINITIONS: Bill Inmon, considered to be the father of data warehousing provides the following definition
“A data warehouse is a Subject-oriented, Integrated, Non-volatile, And time-variant.
Collection of data in support of management’s decision seam Kelly, another leading data warehousing Practitioner defines the data warehouse in the following way.
The data in the data warehouse is,
“Separate
Available
Integrated
Time-stamped
Subject-oriented
Non-volatile
Accessible “
In the data warehouse, data is not stored by operational applications, but by business subjects.
Want to become a master in Cloud Data Warehouse - then enroll in our Snowflake Certification Course
Data inconsistencies are removed; data from diversified operational applications is integrated
Here are some of the items that would need standardization:
The time-variant nature of the data in a data warehouse
Frequently Asked MSBI Interview Questions & Answers
Usually, the data in the data warehous
e is not updated for deleted,
Data granularity in a data warehouse refers to the level of detail data. The lower level details, the finer the data granularity.
Three data levels in a banking data warehouse
Daily detail | monthly summery | quarterly summery |
Account | Account | Account |
Available date | Month | Month |
Amount | Number of transactions | Number of transactions |
Deposit/withdrawal | With drawlsDepositsBeginning balance Ending balance | With drawlsDepositsBeginning balance Ending balance |
RALPH KIMBAL
(Bottom-up approach)W.H 2 NMON
(Top down approach) Here data marts designed first, later from data marts for warehouse Enterprise DWH concentrated first, next data marts
[Related Article: Top Data Warehousing Tools]
The advantages of this approach are:
The disadvantages are:
The advantages of these approaches are:
The disadvantages are:
Difference between data warehouse and OLTP
S.no | OLTP | DWH |
1 | Designed for transaction processing | Designed for decision support |
2 | Volatile | Store non-volatile |
3 | Store current data only | Store historical data |
4 | Store detail data | Store summarized data |
5 | More joins | Less joins |
6 | Normalized (more tables ) | De normalized (less tables ) |
7 | Less indexes | More indexes |
8 | Low-level managers access | High-level manager access |
9 | Read, update, insert, delete | Read |
10 | Access frequency high and in sec | Low, sec to minutes |
Applied on relational sources both data and aggregate information store under relational sources.
Ex: BO, congo’s crystal reports, micro strategy
Here, analysis will be done on multi dimensional application here, data and aggregate information store under multi dimensional sources.
Eg: congo’s SSAS, hyperion, Micro-strategy etc…………
Here data store in relational sources and aggregated values store under multi dimensional sources “cubes”
Eg: cognos, BO, Micro strategic……………
ETL
a) Code based.
Development cost, testing, maintenance
Eg: sas base, sas access, tera data utilities, sql, plsql.
b) GUI based etl- informatica, data stage, abinitio etc …..
a) relational sources: ORACLE, SQL SERVER, TD, DB2, INFORMIX, SY BASE, RED BRICK
b) ERP sources: SAP R/3, DEOPLESOFT, J.D. EDWARDS, BANN, RAMCO MARSHALL.
c) Main frames files: COBOL FILES, IMS FILES, JCL FILES, DB2, JCL
d) File sources: FLAT FILES (TEXT FILES), XML FILES
e) Other sources: WEB LOGIC FILES, TIBCO M2 SERVICES, EXCEL SHEET, and PDF ETC……………….
SSRS | Power BI |
SSAS | SQL Server |
SCCM | SQL Server DBA |
SharePoint | BizTalk Server |
Team Foundation Server | BizTalk Server Administrator |
Name | Dates | |
---|---|---|
MSBI Training | Oct 15 to Oct 30 | View Details |
MSBI Training | Oct 19 to Nov 03 | View Details |
MSBI Training | Oct 22 to Nov 06 | View Details |
MSBI Training | Oct 26 to Nov 10 | View Details |
Ravindra Savaram is a Technical Lead at Mindmajix.com. His passion lies in writing articles on the most popular IT platforms including Machine learning, DevOps, Data Science, Artificial Intelligence, RPA, Deep Learning, and so on. You can stay up to date on all these technologies by following him on LinkedIn and Twitter.