Discover query for SSAS server

<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
  <RequestType>MDSCHEMA_CUBESRequestType>
  <Restrictions />
  <Properties>
    <PropertyList>
      <Catalog>Adventure Works DWCatalog>
    PropertyList>
  Properties>
Discover>

<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
   <RequestType>DBSCHEMA_CATALOGSRequestType>
   <Restrictions />
   <Properties />
Discover>

<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
  <RequestType>MDSCHEMA_CUBESRequestType>
  <Restrictions />
  <Properties />Discover>


 
 

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.

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]