[ MSSQL analysis server ] create SSRS report

Overview

To sum up, creating SSRS report process

First, create data source

Second, create design

The detail steps

1. create `Report Server Project`

รูปภาพ

2) right click > Add New Report

รูปภาพ

3) Specify the connection of the data source

รูปภาพ

4) Type the query, or we can use the Query Builder

รูปภาพ

5) VS will ask whether to create Tabular or Matrix report type

Select Tabular Report Type

6) select fields

รูปภาพ

7) choose table layout

รูปภาพ

8) choose table style ( color )

รูปภาพ

9) Report name

รูปภาพ

10) done

รูปภาพ

11) click `preview` to generate reportreport_preview

[ mssql ] query OLAP cube from MSSQL Management Studio

1. connect > analysis service

Image

2.

– browse cube

– slide and dice

Image

NOTE that multidimensional expression (MDX) can be used to query

For example,

test_mdx

SELECT
{ [Measures].[Extended Amount] }
ON COLUMNS,
{ [Due Date].[Fiscal Quarter - Month].[Fiscal Quarter].&[1].&[7].&[20050713],
[Due Date].[Fiscal Quarter - Month].[Fiscal Quarter].&[1].&[7].&[20050714],
[Due Date].[Fiscal Quarter - Month].[Fiscal Quarter].&[1].&[7].&[20050715] }
ON ROWS
FROM "InternetSales"

[ mssql ] query OLAP cube from the Excel

Prerequisites

– `SQL Server Analysis Services (MSSQLSERVER)` running on server

1. In Excel, Data > From Other Sources

Image

Connect from same machine, fill `localhost`.

If connect from different machine, fill the IP address w/o port w/o instance name ( i.e. 192.168.17.35 ) and the username I used is w/o the workgroup. However, we have to enable firewall port 2383 on the server.

NOTE : just notice the connection string generated in the connection property

Provider=MSOLAP.5;Persist Security Info=True;User ID=Administrator;Initial Catalog=My Customers initial;Data Source=192.168.17.35;Location=192.168.17.35;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error

NOTE : `My Customers initial` is the default name of the Analysis Services database.

2. Select cube that is on the server

Image

3. Add measure, select fact

Image

4. slice and dice

Image

[ mssql ] first OLAP cube

create cube

1. Choose FaceTable, and bring the related table using FK

Image

2. Get suggested measure group, Using Existing Table, Image

3. Create Measure, left the measure all selectedImage

4. select all dimensions

Image

NOTE : Physical table fields shown in picture below

Image

NOTICE :

– From Measure, there is `Count` Measures added

– From Dimensions,

Image

5. Create Date Hierarchy, DimDate always have a hierarchyImage

6. Build > Deploy cube

Browse cube

7. Browse a cube, add measure

Image

8. Slice and dice

Image

ref : developerfusion

[ mssql ] create mdf without ldf

CREATE DATABASE AdventureWorksDW2012 ON (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\AdventureWorksDW2012_Data.mdf') FOR ATTACH_REBUILD_LOG; 

NOTE :

1. place the mdf file in the path that MSSQL has permission, otherwise you will get

The log cannot be rebuilt when the primary file is read-only.

2. I download AdventureWorksDW2012 here, and rename what is in the zip file into `AdventureWorksDW2012_Data.mdf`

ref : codeplex