Guide lines for creating a Cube structure

Use a Real actual data not a sample data in development time, so if there any error that can be happened due to the quality of data like (logical keys, and computed columns that may generate null values,..) will be caught very quickly in the design time.

1. Be sure that all foreign keys has values on primary tables, in case of different sources, create name Query and add any missing data from the parent as unknown values using union, or make an outer join and use the isnull() functions to replace the null values with unknown values or default missing values
2. Use Regular relation (First Option) between the Dimension and the Fact, if the relation in the database is 1 to many (Dimension is one and fact is many)
3. Create different Hierarchically in the Dimension, so you can browse the dimension with different break down.
4. Do not change the source data structure, try to make all change in the Analysis server.
5. Use Fact relation (Second Option) if the Fact and Dimension are the same table
6. User Many-to many (Third Option)
   
   
   
   relation if you have a Fact want to make a relation with Dimension via another Fact that represented on a Measure group
   
   
   
   
   
   
   
   
7. Use Reference relation
   
   
   
   (Last tricky Option), if you want to link a Fact with Dimensions, that has indirect relation with the Fact dimension, this scenario will happen if you have multiple measure groups but if you have only one Dimension group, you can create a hierarchy in the dimension (in the development time, please untick materialized, with big data)
   
8. Always make the Time dimension in the Server not in source database.
9. Avoid big dimensions, do not try to construct dimension based on transaction tables, and if you did, do it in the group and summary level, not in the transaction level.

SQL 2008

Case
org_type =
CASE org_type
WHEN 'OTHER' THEN 'OTHERS'
WHEN 'M' THEN 'Mountain'
WHEN 'T' THEN 'Touring'
WHEN 'S' THEN 'Other sale items'
ELSE org_type
END


 getting last word has '\'
Select RIGHT(UploadPhysicalPath, CHARINDEX ('\',REVERSE (UploadPhysicalPath))-1),UploadPhysicalPath
  FROM [CLProjects].[dbo].[tbl_Projects]

MDX Queries 2

TOPCOUNT

SELECT TOPCOUNT([DimCreationTime].[Year].[Year], 2,
[Measures].[NQ Download Count]) ON COLUMNS
, NON Empty [NQ Download].[Source].[Source] On ROWS
FROM [LRRDownloadsCube]

--------------------------------------
Cube Calculations {new Name Set}

// No select or dimensions

TOPCOUNT([Customer - Territory].[Name].[Name], 10,
[Measures].[Sub Total])

MDX Queries 1

Using Axis & Dimensions
SELECT NON EMPTY

[Measures].[NQ Learning Resource Publish To Count] ON COLUMNS
, NON EMPTY
([Tbl Status].[STS Value].[STS Value].ALLMEMBERS * [Tbl Copyright].[CPR Label].[CPR Label].ALLMEMBERS ) ON ROWS


FROM [LRRCreationCube]

-----------------------------------
Using tuples

SELECT // select a cell (tuple)
NON Empty
([Measures].[NQ Learning Resource Publish To Count] , [Tbl Status].[STS Value].&[Live],[Tbl Publish To].[PUB Value].&[TaLe]) ON COLUMNS

,
NON Empty [Tbl Copyright].[CPR Label].ALLMEMBERS ON ROWS // This axis is optional
FROM [LRRCreationCube]

note &[TaLe]

------
Using Set

SELECT
NON Empty
{
([Measures].[NQ Learning Resource Publish To Count],[Tbl Status].[STS Value].&[Live],[Tbl Publish To].[PUB Value].&[TaLe])
,
([Measures].[NQ Learning Resource Publish To Count],[Tbl Status].[STS Value].&[Live],[Tbl Publish To].[PUB Value].&[Public])
} ON COLUMNS

,
NON Empty
[DimCreationTime].[Year].ALLMEMBERS ON ROWS

FROM [LRRCreationCube]

------

Calculated member

WITH MEMBER Measures.Count10 AS
[Measures].[NQ Learning Resource Publish To Count]*10

SELECT NON Empty
{[Measures].[NQ Learning Resource Publish To Count],Measures.Count10} on columns
,NON Empty
[Tbl Status].[STS Value].&[Live]* Filter( [Tbl Copyright].[CPR Label].[CPR Label].ALLMEMBERS, Instr( [Tbl Copyright].[CPR Label].currentmember.Properties( 'Member_Caption' ), 'Share' ) > 0 ) ON ROWS

FROM [LRRCreationCube]
--------------------------------------------
Hint: Comma=Tuple, brace {}= Set

----------------------------------------------

Using Where
it is like slicing (adding another non displayed dimension to slice the result)

select Non Empty
[Measures].[NQ Download Count]
on COLUMNS
,
Non Empty

(
[NQ Download].[Source].&[PUBLIC]
,[DimRegionUsersCombined].[Org Type].[Org Type]
,[DimCreationTime].[Year].[Year]

) on ROWS

from [LRRDownloadsCube]

//Remove the &[PUBLIC] member value and added to where condition and will give the same effect, except will not appear

//where [NQ Download].[Source].&[PUBLIC]


--------------------------------------------
Using Filter

• SELECT NON Empty

[Measures].[NQ Learning Resource Publish To Count] on columns

,[Tbl Status].[STS Value].&[Live]* Filter( [Tbl Copyright].[CPR Label].[CPR Label].ALLMEMBERS, Instr( [Tbl Copyright].[CPR Label].currentmember.Properties( 'Member_Caption' ), 'Share' ) > 0 ) ON ROWS

FROM [LRRCreationCube]

• SELECT NON Empty

filter([DimCreationTime].[Year].[Year],[Measures].[NQ Download Count]>3000)
on COLUMNS
from [LRRDownloadsCube]

• SELECT NON Empty

filter(
([NQ Download].[Source].[Source],[DimCreationTime].[Year].[Year],[Measures].[NQ Download Count])

,[Measures].[NQ Download Count]>3000)

on COLUMNS
,Non Empty
[NQ Learning Resource Live].[RES Depositor].ALLMEMBERS on rows
from [LRRDownloadsCube]

SQL 2008 Admin Tasks

SHRINK the LOG

USE [master]
GO
ALTER DATABASE [TestDb] SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE(TestDbLog, 1)
ALTER DATABASE [TestDb] SET RECOVERY FULL WITH NO_WAIT
GO

samples

USE LRR_PROD
GO
ALTER DATABASE LRR_PROD SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE(LRR_Log, 1)
ALTER DATABASE LRR_PROD SET RECOVERY FULL WITH NO_WAIT
GO


USE cliwebservices
GO
ALTER DATABASE cliwebservices SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE(cliwebservices_log, 1)
ALTER DATABASE cliwebservices SET RECOVERY FULL WITH NO_WAIT
GO







Backup 

BACKUP DATABASE [db_name] TO DISK = N'\\otfvclicwebuat\webuat\databasesbackup\Web2ServicesPortal.bak' WITH NOFORMAT, NOINIT, NAME = N'CLProjects-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO





Restore
RESTORE DATABASE[db_name] FROM DISK = N'C:\Projects\clic\Dashboard\BI\Others (do not check in)\db\MUM2008_PROD.bak' WITH FILE = 1, MOVE N'MUM08_PROD_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.OTP3222_MSSQL_08\MSSQL\DATA\[db_name].mdf', MOVE N'MUM08_PROD_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.OTP3222_MSSQL_08\MSSQL\DATA\[db_name]_1.ldf', NOUNLOAD, STATS = 10
GO