OLAP

Database > OLAP

Short for Online Analytical Processing, a category of software tools that provides analysis of data stored in a database. OLAP tools enable users to analyze different dimensions of multidimensional data. For example, it provides time series and trend analysis views. OLAP often is used in data mining.

The chief component of OLAP is the OLAP server, which sits between a client and a database management systems (DBMS). The OLAP server understands how data is organized in the database and has special functions for analyzing the data. There are OLAP servers available for nearly all the major database systems.

Microsoft SQL Server Analysis Services is part of Microsoft SQL Server, a database management system. Microsoft has included a number of services in SQL Server related to business intelligence and data warehousing. These services include Integration Services and Analysis Services. Analysis Services includes a group of OLAP and data mining capabilities.

Oracle Essbase is the industry-leading multi-dimensional online analytical processing (OLAP) server, providing a rich environment for effectively developing custom analytic and enterprise performance management applications. By leveraging its self-managed, rapid application development capabilities, business users can quickly model complex business scenarios. For example, line-of-business personnel can simply and rapidly develop and manage analytic applications that can forecast likely business performance levels and deliver "what-if" analyses for varying conditions. Oracle Essbase supports extremely fast query response times for vast numbers of users, large data sets, and complex business models.

Analysis Services tutorial. Creating OLAP cube. Introduction to data warehouse

WITH (SQL Server)

SQL Server > DML > WITH

Specifies a temporary named result set, known as a common table expression (CTE). This is derived from a simple query and defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement.
This clause can also be used in a CREATE VIEW statement as part of its defining SELECT statement.
A common table expression can include references to itself. This is referred to as a recursive common table expression.






Examples:

1. Creating a simple common table expression

-- Define the CTE expression name and column list.

WITH Person_CTE (PersonID, PersonName, PersonSalary)

AS
-- Define the CTE query.
(
Select
 PersonID, PersonName, PersonSalary
FROM
 Person
)

-- Define the outer query referencing the CTE name.

SELECT

 *
FROM
 Person_CTE
ORDER BY
 PersonID;

2. Using a recursive common table expression to display multiple levels of recursion

WITH Emp(superiorid, id, username) AS

(
SELECT superiorid, id, username
FROM User
WHERE superiorid IS NULL

UNION ALL

SELECT e.superiorid, e.id, e.username
FROM User AS e
INNER JOIN IS_User AS e1
ON e.superiorid = e1.id
)

SELECT superiorid, id , username

FROM Emp
ORDER BY superiorid;

3. Get all children of parent

REPLACE (Transact-SQL)

SQL Server > Built-in Functions > REPLACE

Replaces a specified string value with another string value.

REPLACE ( expression , pattern , replacement )
Example

SELECT REPLACE('sample','ple','');

GO 

Result:sam

Height div to window height with scroll

Java > Height div to window height with scroll

<script type="text/javascript" language="javascript">

    window.onload = function () { SizeDiv(); }

    function getDocHeight() {

        var D = document;

        return Math.max(

        Math.max(D.body.scrollHeight, D.documentElement.scrollHeight),

        Math.max(D.body.offsetHeight, D.documentElement.offsetHeight),

        Math.max(D.body.clientHeight, D.documentElement.clientHeight)
    );

    }

    function SizeDiv() {
        
        document.getElementById('leftCol').style.height = getDocHeight()-175 + "px";
     
    }

</script>


SqlCommand, SqlParameter c#

C# > Data > SqlCommand & SqlParameter 

SqlCommand
Represents a Transact-SQL statement or stored procedure to execute against a SQL Server database.

SqlParameter 
Represents a parameter to a SqlCommand and optionally its mapping to DataSet columns

Example:

SqlCommand myCommand = new SqlCommand("dbo.[sp_copy]", connString);

myCommand.CommandType = CommandType.StoredProcedure;

SqlParameter param = new SqlParameter("@ret_id", SqlDbType.Int);

param.Direction = ParameterDirection.ReturnValue;
myCommand.Parameters.Add(param);

param = new SqlParameter("@Source", SqlDbType.Int);
param.Direction = ParameterDirection.Input;
param.Value = asset_id;
myCommand.Parameters.Add(param);

myCommand.ExecuteNonQuery();

id = Convert.ToInt32(myCommand.Parameters[0].Value);

Generate Method Stub

Visual Studio

Generate Method Stub is an IntelliSense Automatic Code Generation feature that provides an easy way to have Visual Studio create a new method declaration at the time you are writing a method call. Visual Studio infers the declaration from the call.

---

SET NOCOUNT SQL Server

SQL Server > Transact-SQL > SET NOCOUNT           

Stops the message indicating the number of rows affected by a Transact-SQL statement from being returned as part of the results.

SET NOCOUNT { ON | OFF }

Remarks

• ON the count is not returned
• OFF the count is returned