Deleting duplicate records in SQL Server

Example Table:

Empid	Empname
1	A
1	A
2	B
2	B
3	C
4	D

Query:
WITH CTE (Empid, Empname, Duplicaterowcount)
AS
(
SELECT Empid, Empname, ROW_NUMBER() OVER (PARTITION BY Empid, Empname ORDER BY Empid) AS Duplicaterowcount
FROM Emp
)
DELETE from CTE
WHERE Duplicaterowcount > 1
GO

Result:

Empid	Empname
1	A
2	B
3	C
4	D

How to setup a TM1 Server to run as a Windows Service

To setup a TM1 Server to run as a service...
1) Open a command prompt: Start > Run > cmd
2) Navigate to the TM1 bin directory using the cd command. Depending on the version of TM1 you have installed, the default location of the bin directory will be either "C:\Program Files\Applix\bin\" or "C:\Program Files\Cognos\TM1\bin". If you installed TM1 to a different directory, navigate to the bin directory appropriately.

tm1sd.exe -install -n"NameOfTM1Server" -z"PathtoTm1s.cfgFile"

NameOfTM1Server (after the -n) is the name you want to give to this TM1 Server service. If the name you are going to use contains any spaces, this value must be specified in double quotes.
The PathtoTm1s.cfgFile (after the -z) is the path to the tm1s.cfg file for this the TM1 Server you are setting up. This parameter value MUST be surrounded by double quotes if there are any spaces in the path name.

For example:
tm1sd.exe -install -n"TM1 Production Server" -z"C:\Program Files\Cognos\TM1\Custom\TM1Data\PData"

If the command is successful, it will return a message indicating so. For example:
TM1 Server / tm1 production server installed.

4) You can then go into the services listing, and you will see the new TM1 Server service you setup. Following the above example, the service would be listed as:
TM1 Server / tm1 production server
You can then configure the service to run automatically, and to run under a specific domain user.

NOTE: If you are setting up a completely new TM1 Server for which you don't already have a tm1s.cfg file, you can copy the tm1s.cfg from the Planning Sample TM1 Server and modify it accordingly. At a minimum, you will need to modify the ServerName, DataBaseDirectory, and PortNumber parameter values.

If the service does not start (error message stating the service cannot be started), starting the same TM1 Logical server as a desktop application will usually provide more detailed information on the error.


If you wish to remove a TM1 Server service (again, following the example above), follow steps 1 and 2, then type the following command (substituting appropriate values where needed):

tm1sd.exe -remove -n"NameOfTM1Server"

The NameOfTM1Server (after the -n) is the name of the TM1 Server service you wish to remove. If the name you are going to use contains any spaces, this value must be specified in double quotes.

For example:
tm1sd.exe -remove -n"TM1 Production Server"

Overview of TM1 Servers

We have to concentrate mainly on 3 components:
1) TM1 Admin Server
2) TM1 Server/Servers
3) TM1 Clients

There are 3 steps mainly takes place in the TM1 servers.

1) TM1 servers register with TM1 Admin server
2) TM1 clients contact the TM1 admin server and receive information about the TM1 servers that are available.
3) TM1 clients establish connections with TM1 servers.

Datawarehousing FAQs

What is Data Warehousing?
A data warehouse is the main repository of an organization's historical data, its corporate memory. It contains the raw material for managements decision support system. The critical factor leading to the use of a datawarehouse is that a data analyst can perform complex queries and analysis, such as data mining, on the information without slowing down the oprational systems. Datawarehousing collection of data designed to support management decision making. Data warehouses contain a variety of data that present a coherent picture of business conditions at a single point in time. It is a repository of integrated information, avaliable for queries and analysis.

What is Business Intelligence (BI)?
Business Intelligence (BI) refers to technologies, applications and practices for the collection, integration, analysis and presentation of business information and sometimes to the information itself. The purpose of business intelligence is to support better business decision making. Thus, BI is also described as a decision support system (DSS).

BI systems provide historical, current and predictive views of business operation, most often using data that has been gathered into a data warehouse or a datamart and occassionally working from operational data.

What is Dimension table?
Dimension table contains textual attributes of measurements stored in the facts tables. Dimension table is a collection of hierarchies, categories and logic which can be used for user to traverse in hierarchy nodes.

What is Dimensional Modeling?
Dimensional data model concept involves two types of tables and it is different from 3rd normal form. This concept uses Facts table which contains the measurements of the business and Dimension table which contains the context (dimension of calculation) of the measurements.

What is Fact table?
Fact table contains measurements of business process. Fact table contains the foreign keys of the dimension tables.
Example, If your business process is "paper production", "average production of paper by one machine" or "weekly production of paper" will be considered as measurements of business process.

What are fundamental stages of data warehousing?
There are four different fundamental stages of data warehousing.

1. Offline Operational Databases
2. Offline Datawarehouse
3. Real Time Datawarehouse
4. Integrated Datawarehouse

Offline Operational Databases:
Data warehouses in this initial stage are developed by simply copying the database of an operational system to an off-line server where the processing load of reporting does not impact on the operational system's performance.

Offline Datawarehouse:
Data warehouses in this stage of evolution are updated on a regular time cycle (usually daily, weekly or monthly) from the operational systems and the data is stored in an integrated reporting-oriented data structure.

Real Time Datawarehouse:
Data warehouses at this stage are updated on a transaction or event basis, every time an operational system performs a transaction (Ex: an order or a delivery or a booking etc.)

Integrated Datawarehouse:
Data warehouses at this stage are used to generate activity or transactions that are passed back into the operational systems for use in the daily activity of the organization.

What are different methods of loading Dimension tables:
There are two different ways to load data in Dimension tables.

1. Conventional (Slow)
2. Direct (Fast)

Conventional (Slow):
All the constraints and keys are validated against the data before it is loaded and this way data integrity is maintained.

Direct (Fast):
All the constraints and keys are disabled before the data is loaded. Once the data is loaded, it is validated against all the constraints and keys. If data is found invalid or dirty, it will not included in index and all future processes are skipped on this data.

Describe the foreign key columns in fact table and dimension table?
Foreign keys of dimension tables are primary keys of entity tables whereas Foreign keys of facts tables are primary keys of dimension tables.

What is Data Mining?
Data Mining is the process of analyzing data from different perspectives and summarizing it into useful information.

What is the difference between a View and a Materialized View?
A View takes the output of a query and makes it appear like a virtual table and it can be used in place of tables.

A Materialized View provides indirect access to table data by storing the results of a query in a seperate scheme object.

What is OLTP?
OLTP (On-Line Transaction Processing) is an application that modifies data instance it receives and has a large number of concurrent users.

What is OLAP?
OLAP (On-Line Analytical Processing) is an application that collects, manages, processes and presents multidimensional data for analysis and management purposes.

What is factless fact table?
A fact table which doesnot contain numeric fact columns is called factless fact table.

What are slowly changing dimensions (SCD)?
SCD applies to cases where the attribute for a record varies over time. There are different types of SCD.

1. SCD1: The new record replaces the original record. Only one record exist in database - current data.
2. SCD2: A new record is added into the customer dimension table. Two records exist in database - current data and previous history data.
3. SCD3: The original data is modified to include new data. One record exists in database - new information is attached with old information in same row.

What is hybrid slowly changing dimension?
Hybrid SCD's are combination of both SCD1 and SCD2. It may happen in a table, some columns are important and we need to track changes for them i.e., capture the historical data for them whereas in some columns even if the data changes, we dont care.

What is BUS schema?
BUS scheme is composed of a master suite of conformed dimension and standardized definition of facts.

What is Star scheme?
Star scheme is a type of organizing the tables such that we can retrieve the result from the database quickly in the warehouse environment.

What is Snow Flake scheme?
Snowflake scheme is a scheme in which each dimension has a primary dimension table, to which one or more additional dimensions can join. The primary dimension table is the only table that can join to the fact table.

Difference between star and snowflake scheme?
Star Scheme: A single fact table with N number of Dimensions, all dimensions will be linked directly with a fact table. This scheme is normalized and results in simple join and less complex query as well as faster results.
Snowflake Scheme: Any dimensions with extended dimensions are known as snowflake scheme, dimensions may be interlinked or may have one to many relationship with other tables. This scheme is normalized and results in complex join and very complex query as well as slower results.

What is the difference between ER modeling and Dimensional modeling?
ER modeling is used for normalizing the OLTP database design. Dimensional modeling is used for de-normalizing the ROLAP/MOLAP design.

What is degenerate dimension table?
If a table contains the values, which is neither dimension nor measures is called degenerate dimensions.

What is junk dimension?
A number of very small dimensions might be lumped together to form a single dimension, a junk dimension - the attributes are not closely related. Grouping of Random flags and text attributes in a dimension and moving them to a seperate sub dimension is known as junk dimension.




Still to come...