Thursday, September 10, 2015

Error attaching MdxStepByStep.mdf for SQL Server 2008 R2

In This article I will show how to attach MDXStepByStep database. I have seen many people face problem when attaching the MDXStepByStep database using the attach_db.sql script.


When you execute attach_db.sql script it gives the below error.

Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "C:\Microsoft Press\MDX SBS\Setup\SQL Server\MdxStepByStep.mdf". Operating system error 5: "5(Access is denied.)".

Please follow the below steps to attach the MDXStepByStep database:

Step 1: Copy only MDXStepByStep.mdf file from “C:\Microsoft Press\MDX SBS\Setup\SQL Server” to “C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA”.

Step 2: Login to your SQL Server with your appropriate credentials, make sure your account must have permission to create database.

Step 3: Right Click on the Database and click on Attach, as shown below.



Step 4: Once you click on attach button it will open a new window. You have to click on add button and then select the MDXStepByStep.mdf file and click ok. You can find below the screenshot.



Step 5: You have added the MDXStepByStep.mdf file but MDXStepByStep.ldf file will be missing for this database because you didn’t copy that file in setp 1. In this step you have to remove the missing file.
Select MDXStepByStep.ldf file and click on remove button and click Ok as shown below.

 

Congratulations you have successfully attached the MDXStepByStep Database.

Queries for Checking OLAP instances

select * from $SYSTEM.DISCOVER_SESSIONS where session_current_database = 'orgCube'

select * from $system.discover_connections

select * from $system.discover_commands
select * from $system.discover_object_memory_usage
select * from $system.discover_object_activity
select * from $system.discover_locks

Sunday, September 6, 2015

Star schema and Snowflake schema in SSAS 2014


Below table will show the difference between the Star Schema and Snowflake Schema.


Star Schema: Each and every dimension present in the Data Source View (DSV) is directly linked or related to Fact or measures table.
Snowflake Schema: Some dimensions present in the Data Source View (DSV) are linked directly to the fact table and some dimensions are indirectly linked to fact tables (with the help of middle dimensions). For instance in [Adventure Works DW 2014], [Dim Product sub category] is indirectly related to [fact Internet Sales] with the help of [Dim Products]. 
STAR SchemaSnowflake Schema
Centrally located fact table surrounded by DE normalized dimension tableCentrally located fact table surrounded by the normalized dimension table
In Star Schema, All dimensions will be linked directly with fact tableIn Snowflake Schema, some dimensions are linked directly to the fact table and some dimensions are indirectly linked to fact tables (with the help of middle dimensions)
It is easy to understand the designIt is difficult to understand
Increase the query performance because we can extract the data with less number of joinsWe have to join more tables to extract data so more joins
We mostly see the STAR Schemas in small data base or we say small companiesMost of the Big Companies or any big database will always belongs to Snowflake Schema.
By DE normalizing the database we can convert the Snowflake Schema to star schemaBy normalizing the database means dividing the table’s data further using primary and foreign keys we can convert the star schema to Snowflake Schema easily.
STAR FLAKE: A hybrid structure that contains a mixture of star schema (DE normalized data) and snowflake schema (normalized data).

STAR SCHEMA in SSAS EXAMPLE

We can see from the below figure [Dim Production], [Dim Customer], [Dim Product], [Dim Date], [Dim Sales Territory] tables are directly attached to [Fact Internet Sales]. So, this is the perfect example for the star schema in SSAS
SSAS Star Schema

SNOW FLAKE SCHEMA in SSAS EXAMPLE

We can see from the below figure [Dim Currency], [Dim Customer], [Dim Date], [Dim Product] tables are directly attached to [Fact Internet Sales] but [Dim Product Sub Category] and [Dim Product Category] are indirectly connected.
SSAS SnowFlake Schema
Thank you for Visiting Our Blog

Create OLAP Cube in SSAS



In this article we are going to show you, How to create OLAP cube in SQL Server Analysis Services (SSAS) using cube Wizard.
Cube is a combination of Measures (Derived, Calculated Measures) and Dimensions, calculations, perspectives, actions, partitions, key performance indicators (KPIs) and translations.

Create OLAP Cube in SQL Server Analysis Services

Within the Solutions Explorer, Right click on the Cube folder will open the context menu. Select New Cube option as shown in below screenshot.
Create OLAP Cube in SSAS 1
It will open the Cube wizard with the welcome page. If you don’t want to see this welcome page again check the Don’t show this Page again option below.
Create OLAP Cube in SSAS 2
Click next button
Select Creation Method
Within the cube wizard this page will give you 3 options to choose
  • Use Existing Tables: If you select this option, then cube will use the existing tables in the Data Source View.
  • Create an Empty Cube: This option will create an empty cube with no Measures and Empty Dimensions. After completing the cube creation we have to add them one by one.
  • Generate Tables in the Data Source: It will generate tables in the Data Source directly.
Create OLAP Cube in SSAS 3
It is always better to choose the option (Create an Empty Cube), that’s what I did in my experience and I hope this is the best approach for any developer. Click on Next Button
Select the Data Source View
This page is used to select the Data Source View for our Empty cube. Here we can select the data source if we already created or else we can select none and later we can create and use it. Again its developer’s choice but it is always good practice to create the data source and data source views at the beginning.
Create OLAP Cube in SSAS 4
Here we are selecting the Data Source View we created in the previous article. Please referSSAS Data Source View article to understand How to create the Data Source View in SSIS
Click Next button and rename the cube as per your requirement.
Create OLAP Cube in SSAS 5
Click finish button to complete the cube creation.
Lets look at our Solution Explorer to check for newly created Cube.
Create OLAP Cube in SSAS 6

Creating Cube Using Existing Tables

Here we are selecting the Existing Tables option to show you, How this gone work.
Create OLAP Cube in SSAS 7
Click on the Next button
Select Measure Group Tables
Select the already created (existing) Data Source View. Here also we are selecting the Adventure Works DW2014, which we created in our previous article.
Create OLAP Cube in SSAS 8
Now select the Measure Tables (Measure Groups or Fact tables) from the list of available tables in the data Source View.
Create OLAP Cube in SSAS 9
Click next button.
Cube wizard will take you to next page to select the individual Measures from Measure Groups.
Create OLAP Cube in SSAS 10
Here we selected some random Measures and click on Next button.
Select Existing Dimension
This page allows us to select the required dimensions for cube creation from the list of available dimensions
Create OLAP Cube in SSAS 11
Click on next button and provide the cube name as per your organization requirements.
Create OLAP Cube in SSAS 12
Click finish button and see whether we created the cube successfully or not
Create OLAP Cube in SSAS 13
Thank you for visiting my Blog

Saturday, September 5, 2015

SSAS - How to create Analysis Database Snapshot or Cube Snapshot

1.      Background

The purpose of this article is to provide detail information about how to create an Analysis Database Snapshot or cube snapshot in an automated way. We are going to use Powershell script with a SQL agent job to automate the Analysis DB snapshot process. Here SQL Agent job executes PowerShell script that will first script out the source analysis DB into .xmla file and then it will rename the Analysis DB with postfix _YYYYMMDD as well as it change the storage mode from ROLAP to MOLAP wherever it is required in the newly created XMLA file and finally deploy the modified XMLA on Source analysis server instance. In this way, it will create a new analysis DB (replica of existing DB) that can be utilized as snapshot of current analysis DB after processing the database.

2.      Why Analysis Database or Cube Snapshot is required?

There are several reasons for maintaining the Cube Snapshot. Following are the few of the them:
1.      To maintain Analysis DB/cubes on weekly, monthly and annual basis. E.g CubeName_2010, CubeName_2011,CubeName_23032013
2.       Sometimes, we need to maintain read only copy of Cubes for Analysis and Reporting purpose on which only any specific group will have access. Due to this, we can avoid the access to main Cubes.
3.       Snapshots can be utilized for analysis & reporting of historical and timelines data

3.      PowerShell Script for Analysis Database Snapshot

Below is the PowerShell script that will do following operations:
·         It connects to SQL Server Analysis Server instance and script out the given Analysis DB into .XMLA file. Here the output folder is C:\Data
·         It adds the Postfix (_YYYYMMDD) to Analysis Database name, changes ROLAP to MOLAP storage mode and disable theproactive caching.
Note: Script will do all the changes in newly created .xmla file
·         Deployment of Analysis Database with postfix _YYYYMMDD into SQL Analysis instance.
·         Processing of the newly created Analysis database so that we can browse the data.

param
(
[string] $servername,
[string] $DBName,
[string] $OutputFilePath
)

##$serverName = "MyAnalysisServerName"
##$OutputFilePath = "C:\data\"
##$DBName = "MyAnalysisDB"


## load the AMO and XML assemblies into the current runspace
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") > $null
[System.Reflection.Assembly]::LoadWithPartialName("System.Xml") > $null
$dateStamp = (get-Date).ToString("yyyyMMdd")

## connect to the server
$svr = new-Object Microsoft.AnalysisServices.Server
$svr.Connect($serverName)
foreach ($db in $svr.Databases)

{

    if ($db.name.tostring() -eq $DBName)
    {
    ##write-host "---------------------------------------------"
    ##write-host "Scripting: " $db.Name
    $xw = new-object System.Xml.XmlTextWriter("$($OutputFilePath)DBScript_$($db.Name)_
$($dateStamp).xmla", [System.Text.Encoding]::UTF8)
    $xw.Formatting = [System.Xml.Formatting]::Indented
    [Microsoft.AnalysisServices.Scripter]::WriteCreate($xw,$svr,$db,$true,$true)
    $ReplaceFilePath = "$($OutputFilePath)DBScript_$($db.Name)_$($dateStamp).xmla"
    $NewDBName = "$($db.Name)_$($dateStamp)"
    $xw.Close()
    }

}
$svr.Disconnect()

##write-host "Scripting completed: " $NewDBName

##write-host "---------------------------------------------"
##write-host "Started  : Changing Analysis DB name,storage mode and Proactive caching: "

(Get-Content $ReplaceFilePath) |
Foreach-Object {$_ -replace "<StorageMode>Rolap</StorageMode>", "<StorageMode>Molap</StorageMode>"
} |

Set-Content $ReplaceFilePath


(Get-Content $ReplaceFilePath) |
Foreach-Object {$_ -replace "<Enabled>true</Enabled>", "<Enabled>false</Enabled>" } |

Set-Content $ReplaceFilePath


(Get-Content $ReplaceFilePath) |
Foreach-Object {$_ -replace "AnalysisDBName", $NewDBName} |

Set-Content $ReplaceFilePath


##write-host "Completed  :  Changing Analysis DB name,storage mode and Proactive caching: "

###############################################################################################

##write-host "---------------------------------------------"
##write-host "Started  :  Creaitng Cube Snapshot: " $NewDBName

$ReplaceFilePath = $ReplaceFilePath.Replace('"',"")

$servername = $servername.Replace('"',"")


$qry = [string](get-content $ReplaceFilePath)

$amo = "Microsoft.AnalysisServices"

[System.Reflection.Assembly]::LoadWithPartialName($amo) > $null

$svr = New-Object Microsoft.AnalysisServices.Server

$svr.Connect($servername)

$svr.Execute($qry)

##write-host "Completed  :  Creaitng Cube Snapshot: " $NewDBName


###############################################################################################




[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") >$NULL
$server = New-Object Microsoft.AnalysisServices.Server
$server.connect($servername)
$database=$server.databases
$advdb=$database[$NewDBName]


$date1=get-date
##write-host "---------------------------------------------"
##write-host "Processing Database : $advdb"
$ProcessType="ProcessFull"
$advdb.Process($ProcessType)
$date2=get-date
##write-host "Process

                                                  -----------------------End of the script----------------------

4.      Automation of Analysis Database/Cube Snapshot

We can automate the Analysis DB/Cube Snapshot creation process by using SQL Agent job. For Automation, We need to run the above mentioned PowerShell script into SQL Agent job step with required input parameters. For running the PowerShell script, we don’t need to copy and paste complete PowerShell script into the job step instead gives the full path of the PowerShell script with required input parameters. Note Step type should be PowerShell          

Analysis Database Snapshot Job Script:

                USE [msdb]
GO

/****** Object:  Job [Analysis_DB_Snapshot]    Script Date: 07/24/2013 16:00:53 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 07/24/2013 16:00:53 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]'AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL',@name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Analysis_DB_Snapshot',
            @enabled=1,
            @notify_level_eventlog=0,
            @notify_level_email=0,
            @notify_level_netsend=0,
            @notify_level_page=0,
            @delete_level=0,
            @description=N'No description available.',
            @category_name=N'[Uncategorized (Local)]',
            @owner_login_name=N'Vishal-PC\Vishal', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Creating snapshot of Analysis DB]    Script Date: 07/24/2013 16:00:54 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Creating snapshot of Analysis DB',
            @step_id=1,
            @cmdexec_success_code=0,
            @on_success_action=1,
            @on_success_step_id=0,
            @on_fail_action=2,
            @on_fail_step_id=0,
            @retry_attempts=0,
            @retry_interval=0,
            @os_run_priority=0, @subsystem=N'PowerShell',
            @command=N'G:\PSScript\ScriptASDB_Job.ps1  ".\SQL2012"  "SSAS_Study" "C:\Data\"',
            @database_name=N'master',
            @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

6.     Conclusion

By using above mentioned steps, we can automate the Analysis Database/Cube snapshot process by using PowerShell script.

Fig Showing Analysis DB and its Snapshots
 


----------------------------------------------------End of Document---------------------------------------------------