SQL 2005 (SQL 2008) Analysis Services Server Side Tracing

  With Analysis Services 2005 (2008) you can collect traces without using gui this feature is known as Server Side Tracing. In this article we’ll talk about how to achieve Server Side Tracing. How to create a Server Side Trace Open SQL Server profiler connect to Analysis Services and in an Event Selection define the…

19

Slowly Changing Dimension using SSIS

Surrogate Keys Also known as meaningless keys, substitute keys, non-natural keys, or artificial keys. A surrogate key is a unique value, usually an integer, assigned to each row in the dimension. This surrogate key becomes the primary key of the dimension table and is used to join the dimension to the associated foreign key field…

17

Configuring Datapump in IIS 7

Configuring HTTP Access to SQL Server 2005 or SQL 2008 Analysis Services on Microsoft Windows Server 2008 Getting BinariesCopy the contents of the %Installation folder%\OLAP\bin\isapi directory into the folder you would like to become the base for the virtual directory in IIS. In this example, we are going to copy all the files from the…

11

Different Kinds of SSAS Processing in simple words…..

Often time I been asked this quest – what will be the impact of processadd on indexes or aggregations, do I need to recreate aggregations or Indexes after creating Process Update or what is the right approach of handling processing, so thought of writing this article. Here are the major Process Types in SQL 2005…

10

SQL Server Analysis Services Port (SQL 2005 / 2008)

  Default Port:2383 You can change default Port for AS in msmdsrv.ini file of the Instance. Port Used by SQL Server Browser Services for SSAS is 2382   How to determine on which port AS is running? -Open Task Manager Get the PID for msmdsrv.exe -Open command Prompt type netstat /ao >>c:\output.txt -Look for the…

9

How to Deal with Corruption in Analysis Services

  I had seen few scenario where you perform any action on Analysis Services you get weird Error Message like some store is corrupted Sample Error: An error occurred when instantiating a metadata object from the file, ‘\\?\J:\Program Files\Microsoft SQL Server\MSAS10.SQLInst\OLAP\Data\dbDev.0.db\testdim.2.dim\DimensionPermission 1.0.perm.xml’. To resolved Database Corruption follow below mentioned steps: Workaround 1 – Take the…

6

SSAS Monitoring Tool– ASTrace

Guys, We have noticed customers asking a tool for  Monitoring SSAS Activities;  So we have added new features to an existing ASTrace of SQL 2005 and introduced a new version for SQL 2008/R2 & SQL 2012(MOLAP and Tabular). With this tool you can monitor minimum to maximum SSAS Information.  Architecture is pretty simple, ASTrace runs…

6

Running AS Command(XMLA / MDX) as a Batch Job Using SSIS Package

  – Get ASCMD for right version 2008 2005   -Create a file with XMLA / MDX Command and give extension as xmla/mdx   -Create one batch file with command text as “d:\asjob\ascmd -S karan2k8-64\sql2005 -i d:\asjob\backup.xmla” In my instance- ASCMD is located in D Drive ASJob folder that’s why I had given it in…

6

Azure Data Factory: Detecting and Re-Running failed ADF Slices

  Recently I came across  a scenario where I need to detect failed slices of all Datasets in Azure Data Factory, in my case I need to detect for last 3 months and the number of slices was around 600+, they failed due to validation error as the source data wasn’t present and after a…

4

Cleared SSAS Maestro (MCM)

  Finally achieved highest certification in SSAS world. What is the SSAS Maestros? The SSAS Maestro program was created as a way to share the lessons learned by enterprise customers of the SQL Customer Advisory Team (SQLCAT) using complex SQL Server 2008 R2 Analysis Services and a Unified Dimensional Model (UDM). Requirements Because of the…

4