SQL SSAS Dimension relationships

There are four main sql ssas dimension relationships. Regular, Referenced, Many-to-Many and Fact.

Regular - Is used when relationship of a dimension is linked directly to a measure group through the attribute.

Reference - Is used when relationship of a dimension is linked directly to a measure group through a intermediate dimension.

Many-to-Many - Is used when relationship of a dimension is linked through a measure group through an intermediate measure group.

Fact - Is used when relationship of a dimension is linked through a fact table to define the measure group.

If anyone can elaborate further on this, you are welcome.

SQL SSAS Backup

The sql ssas cube is very important to be backed up. This can be done using the sql server manaement studio.

The following steps to BACKUP the ssas cubes.

Connect to the analysis servvices. Right click the cube to be backed up.

Click on Backup. Uncheck File compression --- this is used compress the backup file at the expense of performance.Uncheck the encrytion --- this can be checked if the backup file need to be pasword protected in that case a password has to be provided.
check the file overwrite option based on your requirement

Click Browse in the selected path enter the folder where the backup file to written.
Now click on the script and script action to new query window
This will write a query to query window. Copy this and open a new job in the sql agent.

Scheduling the backup using sql agent

Click New job
provide a name for the job , select step, select type as SQL SERVICES ANALYSIS COMMAND, Enter the server , past the script below

SQL SSAS High Availability

High availability and maximum uptime of ssas cubes, to achieve this we can use Microsoft clustering services (MSCS)

The following are the major advantages of doing so.

1. SSAS Solution will stay online even the operting system is corrupted.

2. SSAS Solution will stay online even when there is a hardware failure.

Maximum error count property - Control flow

The maximum error count Control flow property will allow a package to continue execution even after the errors occured. The maximum number specifies the maximum number of errors allowed.

For example in a sequence container of for loop container the maximum error count can be set.

Report Builder Model

There are two options to create the report builder models.
1. Relational report models
2. ssas report models

relational report models are best created using the bids(business intelligence development studio).

ssas report models are best created using reporting services and ssms, they cannot be created using bids.

creating a report model is a three step process.
using the bids 1. create the data source 2. create the data source view and 3. create the report model.

creating a report ssas report model, using the reporting services, login to http://localhost/reports , select the datasource folder, create a datasource, click on the generate modle button, click on the change location of the model path, click ok to create the model.

OLAP Cube Processing

Olap cube processing

1. The olap cube and the dimensions has to be prcessed to have the updates.
2. This can be done manually or automatic.
3. Manual process --- to open sql server management studio, connect to analysis services, click on the analysis database dimensions first --- dimensions will be listed on the explorer details window on the side, select all right click and process.
4. Now click the dimension --- right click and process.
5. If there are errors in processing please check you data first, for example primary key validation, datetime format.

Now let us see how to automate the process.

1. Using the manual process above click on script to generate the scripts. copy in a notepad.

2. Open microsoft sql server management studio.

3. Connect to the database.

4. Open sql server agent.

5. Click on jobs --- right click and click on new job.

6. Clcik on general --- enter the name of the job, please not you should rights on the database to perform this job.

7. Now click on the step --- click new step, enter the step name, select the type as sql server analysis services command , under run as -- select sql agent service account, now copy the scipt under the command, click ok

8. Click on the schedule -- schedule it.

9. Click on notifications --- select the mail to be notified if the job fails.

Create a olap cube

To create a olap cube

The following is advised to be known
1. Please make sure the tables to be used are in line with your requirement. for example - sales analyis in a company by customer, by product type, by reps and by jobnumber.

2. Also have the calendar table included to view by year,quater,month and date.

3. To create this table please refer to my earlier post in SSAS.

4. After this is done open the sql server business intelligence studio.

5. Click new project --- business intelligence projects --- analysis project --- under browse select the location where the project is been saved.

6. In the data sources --- create a new datasource, use the windows authentication account. In the data source view --- create a new data source select the tables to be used in the cube.

7. In the cube right click and follow the wizard, click on build the cube using a data source, click next --- select the fact (are measure where the numeric values stored e.g., grossprice, totalnet price ) and dimension tables (used for filter e.g., by product type, by invoicdate) --- clik next
now select the measures to be displayed, click next --- it will required to select atleast one dimension ( note dimensions can be created later) --- click next --- give a name for the cube and finish.

8. Now having created the cube with one dimension additional dimensions can be created, but before, click on the green arrow to process the cube, you might get an error if you have not set the deployment server. Go to menu project --- properties and click on deployment and specify the server name e.g., 192.0.0.111\MSSQ1 do not forge the instance name.

9. Now press F5 or click on the green arrow and process the cube, you should see something similar to image below.

10. Addition dimensions can be added dimension right click --- new dimension --- click next --- build the dimension using a new source -- check on autobuild --- click next --- click on standard dimension or time dimension --- click next --- select the table --- check the field you need to filter --- e.g., by product type.

repeat step 10 for additional dimensions.

Hosting OLAP Reports

To host olap reports on windows server 2008 using IIS Manager

Make sure you have the olap cube on the asp.net project and wanted to host, follow the steps below.

1. Copy the application folder containing the bin, obj, my project folder and files default.aspx,defualt.aspx.vb, to the c:\inetpub\wwwroot

2. Go to administrative tools --- iis manager

3. Open default web site --- click the folder you had copied.

4. Right click the folder you had copied --- click convert to application.

5. Change the application pool to --- Classic .NET AppPool

6. Make the sure the folder in c:\inetpub\wwwroot\ has appropriate rights to access the hosted application.

SSAS-Calculation member error

When accessing calculation tab in Business Intelligence Development Studio (BIDS) I am getting error "Unexpected error occurred:Error in application".

Fix / Workaround/Solution

Most likely this error is caused by mismatched SSAS dlls: msmdlocal.dll and msmgdsrv.dll. To fix this move this 2 dlls from folder:

c:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\
To folder:
c:\Program Files\Common Files\System\Ole DB\

Note: Before replacing any dll make sure you backup them to safe location.

This solution worked for me.

Calendar Table for olap dates - sql script

Table for OLAP date
--------------------
How It Works:

GetLastFridayOfMonthForYearRange.sql
This query populates a table called FinancialMonths with the date of every last Friday in a 12 month period based on financial year range (startdate, end date) entered.

Last Friday is found as follows: the query check when you start a new month (1/?/????) (@currentMonth <> month(@StartDate) it then takes 7 days off the current date (@startDate) and loops backwards until it finds the first Friday ( last Friday of prev month) and inserts into new table

DECLARE @StartDate as datetime
DECLARE @endDate as datetime
DECLARE @currentMonth as integer
SET @StartDate = '2010-07-01'
SET @EndDate = '2011-06-30'

DECLARE @lastFridayOfPrevMonthDate as datetime
DECLARE @getLastFridayDateCounter as datetime


SET @currentMonth = month(@StartDate)
seT @lastFridayOfPrevMonthDate = @StartDate


WHILE @startDate < @endDate BEGIN --GET LAST FRIDAY CODE IF @currentMonth <> month(@StartDate)
BEGIN
SET @getLastFridayDateCounter = dateadd(dd,-1,@StartDate)
WHILE @getLastFridayDateCounter < @StartDate BEGIN IF UPPER(DATENAME(WEEKDAY,@getLastFridayDateCounter)) = 'FRIDAY' BEGIN SET @lastFridayOfPrevMonthDate = @getLastFridayDateCounter --END LOOP SET @getLastFridayDateCounter = @StartDate + 1 END ELSE BEGIN SET @getLastFridayDateCounter = @getLastFridayDateCounter - 1 END SET @currentMonth = month(@StartDate) END --insert into FinancialMonth Select @lastFridayOfPrevMonthDate END SET @startDate = @startDate +1 END --last month end date is not a last friday insert on its own --insert into FinancialMonth Select @endDate ------------------------------------------------------------------------------------ Olap Solution.sql A Record needs to be inserted on a per day basis in Calendar table. The query loops per day from the startdate of given financial year to the end date. For each day is checks if this date is a last Friday (from table above previously populated) and upates set of variables relating.Similiarly every day and weekly variables are updated. DECLARE @StartYearDate as datetime DECLARE @EndYearDate as datetime DECLARE @quarterStartDate as datetime DECLARE @quarterCount as integer DECLARE @day_of_year as integer DECLARE @currentMonth as integer DECLARE @lastFridayOfPrevMonthDate as datetime DECLARE @getLastFridayDateCounter as datetime DECLARE @monthQuarterCount AS INTEGER Declare @monthStartDate as datetime Declare @weekDate as datetime declare @weekNo as integer Declare @dayOfQuarter as integer Declare @dayOfMonth as integer Declare @weekOfYear as integer Declare @monthOfYear as integer Declare @monthOfQuarter as integer Declare @quarterOfYear as integer Declare @financialYear as varchar(20) -------------------------------------------------------------------------------- --update these 3 variable prior to calendar insert on a per fin year basis ----- -------------------------------------------------------------------------------- SEt @financialYear = '2010-2011' SET @StartYearDate = '2010-07-01' SET @EndYearDate = '2011-06-30' --delete from tblbsbcalendar_Marguerite -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- SET @quarterCount = 1 SET @day_of_year = 0 SET @currentMonth = month(@StartYearDate) SET @monthQuarterCount = 1 seT @lastFridayOfPrevMonthDate = @StartYearDate SET @quarterStartDate = @StartYearDate SET @weekDate= @StartYearDate SET @weekNo = 1 set @dayOfQuarter = 0 set @dayOfMonth = 0 set @weekOfYear = 1 set @monthOfYear = 1 set @monthOfQuarter = 1 set @quarterOfYear = 1 WHILE @StartYearDate <= @endYearDate BEGIN --ALWAYS UPDATE THESE COUNTERS SET @day_of_year = @day_of_year + 1 SEt @dayOfQuarter = @dayOfQuarter + 1 set @dayOfMonth = @dayOfMonth + 1 --every financial month update variables IF Exists(Select LastFriday from FinancialMonth where LastFriday = @StartYearDate-1) BEGIN SELECT @lastFridayOfPrevMonthDate = LastFriday +1 from FinancialMonth where LastFriday = @StartYearDate-1 SET @dayOfMonth = 1 Set @monthOfYear = @monthOfYear + 1 SEt @monthOfQuarter =@monthOfQuarter + 1 IF @currentMonth = 12 SET @currentMonth = 1 ELSE SET @currentMonth = @currentMonth + 1 -- UPDATE QUARTER COUNT IF NECESSARY WHEN MONTH CHANGES SET @monthQuarterCount = @monthQuarterCount + 1 IF @monthQuarterCount > 3
BEGIN
Set @quarterOfYear = @quarterOfYear + 1
SET @monthQuarterCount = 1
SEt @dayOfQuarter = 1
SEt @monthOfQuarter = 1
SET @quarterStartDate = @lastFridayOfPrevMonthDate + 1
SET @quarterCount = @quarterCount + 1
END
END
--every week updates
IF UPPER(DATENAME(WEEKDAY,@StartYearDate)) = 'SATURDAY'
BEGIN
set @weekDate= @StartYearDate
SET @weekNo = @weekNo + 1
Set @weekOfYear = @weekOfYear + 1
END



-- INSERT CODE---------------------------------------------------------------------------------------------------------------------------
insert into tblcalendar_table

SELECT @StartYearDate as pk_date,
DATENAME(weekday, @StartYearDate) +', ' + DATENAME(MM, @StartYearDate) + ' ' + DATENAME(DD, @StartYearDate) + ' ' + DATENAME(YY, @StartYearDate) as Date_Name,
cast(cast(year(@StartYearDate) as varchar) + '-01' + '-01' as datetime) as [Year],
'Calendar ' + @financialYear as Year_Name,
@quarterStartDate as Quarter,
'Quartor ' + cast(@quarterCount as varchar) + ', ' + @financialYear as Quarter_Name,
@lastFridayOfPrevMonthDate as [Month],
case @currentMonth
When 1 then 'January'
When 2 then 'February'
When 3 then 'March'
When 4 then 'April'
When 5 then 'May'
When 6 then 'June'
When 7 then 'July'
When 8 then 'August'
When 9 then 'September'
When 10 then 'October'
When 11 then 'November'
When 12 then 'December'
END + ' ' + @financialYear,
@weekDate as week,
'Week ' + cast(@weekNo as varchar) + ', ' + @financialYear as week_name,
@day_of_year as day_of_year,
'Day ' + cast(@day_of_year as varchar) as day_of_year_name,
@dayOfQuarter as Day_of_Quarter,
'Day ' + cast(@dayOfQuarter as varchar) as day_of_quarter_name,
@dayOfMonth as day_of_month,
'Day ' + cast(@dayOfMonth as varchar) as day_of_month_name,
DATEPART(weekday,@StartYearDate) as day_of_week,
'Day' + cast(DATEPART(weekday,@StartYearDate) as varchar) as day_of_week_name,
@weekOfYear as day_of_year,
'Week ' + cast(@weekOfYear as varchar) as day_of_year_name,
@monthOfYear,
'Month ' + cast(@monthOfYear as varchar),
@monthOfQuarter,
'Month ' + Cast(@monthOfQuarter as varchar),
@quarterOfYear,
'Quarter ' + cast(@quarterOfYear as varchar)
SET @StartYearDate = @StartYearDate +1
END