ERROR: SSAS Cube has no linked measure groups

When we work on deploying SSAS cubes, we may come across the following error:

Errors in the metadata manager. The cube has no linked measure groups. Errors in the metadata manager. 
An error occurred when loading the ..... cube, from the file, '\\?\C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data\Sales_sample.0.db\Sales ~.2.cub.xml'.

To resolve this issue perform the below operation:

1. Stop Analysis Service 
2. Navigat to the respective folder where you store Analysis service data "C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data\".
3. Delete the main folder of the Analysis Service database, e.g., if your analysis service db name is Sales. Then delete the folder named Sales in the path.
4. Also delete Sales.cub.xml file ion the path.
5. Restart the Analysis service.
6.  Deploy the cube.

How to view the value of SSIS variable updated from SQL task

How to view the value of SSIS variable updated from SQL?

In order to view the value passed to SSIS Variable from SQL Task add an script task after the SQL Task and include the code given below:

Consider the variable name is "User::IncrementalDate":


Public Sub Main()
        MsgBox(Dts.Variables("User::IncrementalDate").Value)
        Dts.TaskResult = ScriptResults.Success
End Sub

Note: Check User::IncrementalDate variable as ReadOnly variable i n Script task.

Convert Seconds to HH:MM:SS

DECLARE @SECONDS INT = 5200

SELECT CONVERT(CHAR(8),DATEADD(second,@SECONDS,0),108) 'TOS HHMMSS'

How to Apply Read/Write Mode to a Datbase

TO SET TO READ WRITE

-----------------------------------------------------------------

USE MASTER
GO
/*Mark it as Singe User*/
ALTER DATABASE [DATABASE_NAME] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

/*Mark the database as Read Write*/
ALTER DATABASE [DATABASE_NAM] ESET READ_WRITE WITH ROLLBACK IMMEDIATE

/*Mark it back to Multi User now*/
ALTER DATABASE DATABASE_NAME SET MULTI_USER

  

TO SET TO READ ONLY

-----------------------------------------------------------------

USE MASTER
GO
/*Mark it as Singe User*/
ALTER DATABASE [DATABASE_NAME] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

/*Mark the database as Read Only*/
ALTER DATABASE [DATABASE_NAME] SET READ_ONLY WITH ROLLBACK IMMEDIATE

/*Mark it back to Multi User now*/
ALTER DATABASE [DATABASE_NAME] SET MULTI_USER

T-SQL to Filter Records Containing Special Characters and Latin Characters

The below SQL code helps to filter record containing Non-English characters:

SELECT * FROM [Table_Name]
WHERE [Column_Name] LIKE N'%[^ -~]%' collate Latin1_General_BIN

To Check SQL Server Status & Blocking Process

-- 1. To check any blocking in SQL server

SELECT CMD, * FROM SYS.SYSPROCESSES WHERE BLOCKED > 0

-- 2. Check the log space in the server

DBCC SQLPERF (LOGSPACE)

GO

  

-- 3. Check any process is running for long time.

SP_WHO2

Example MDX Calculation for SSAS Cube

Example MDX Calculations

Sales Growth

([Time].CurrentMember, [Measures].[Sales]) - ([Time].CurrentMember.PrevMember, [Measures].[Sales])

PP Growth

([Time].CurrentMember, [Measures].[Sales]) - (ParellelPeriod(Year, 1, [Time].CurrentMember), [Measures].[Sales])

YTD Sales

sum(ytd([Time].CurrentMember), [Measures].[Sales])

YTD Sales Using PeriodsToDate

sum(PeriodsToDate([Time].[Year], [Time].CurrentMember), [Measures].[Sales])

Product Percentage

([Product].CurrentMember, [Measures].[Unit Sales]) / ([Product].CurrentMember.Parent, [Measures].[Unit Sales]) * 100

Product Total Percentage

([Product].CurrentMember, [Measures].[Unit Sales]) / ([Product].[All Products], [Measures].[Unit Sales]) * 100

Average Stock - Brute Force

Sum(Descendants([Time].CurrentMember, [Month]), [Measures].[Quantity]) / Count(Descendants([Time].CurrentMember, [Month]))

Average Stock - Elegant

Avg(Descendants([Time].CurrentMember, [Month]), [Measures].[Quantity])

Closing Period - Brute Force

(Tail(Descendants([Time].CurrentMember, [Month]), 1).Item(0), [Measures].[Quantity])

Closing Period - Elegant

(ClosingPeriod([Month], [Time].CurrentMember), [Measures].[Quantity])

Max for Period

Max(Descendants([Time].CurrentMember, [Month]), [Measures].[Quantity])

Min for Period

Min(Descendants([Time].CurrentMember, [Month]), [Measures].[Quantity])

Moving Average

Avg([Time].CurrentMember.Lag(2):[Time].CurrentMember, [Measures].[Unit Sales])

Products Down

Count(Filter(Descendants([Product].CurrentMember, [Product Name])             , ([Time].CurrentMember, [Measures].[Unit Sales])               < ([Time].CurrentMember.PrevMember, [Measures].[Unit Sales]))) / Count(Descendants([Product].CurrentMember, [Product Name]))

Dynamic Custom Default Member

Tail(Filter([Time].[Month].Members      , Not IsEmpty([Time].CurrentMember)), 1).Item(0)

Properties Query

Filter([Store].[Store Name].Members        , Val([Store].CurrentMember.Properties("Store Sqft")) < 21000)

Products Down Query

select       {{{[Measures].[Products Down]}         * {[Customers].DefaultMember}         * {[Product].DefaultMember}}} on columns      , {[Time].&[1998].&[Q1].&[1]         , [Time].&[1998].&[Q1].&[2]         , [Time].&[1998].&[Q1].&[3]         , [Time].&[1998].&[Q2].&[4]         , [Time].&[1998].&[Q2].&[5]         , [Time].&[1998].&[Q2].&[6]         , [Time].&[1998].&[Q3].&[7]         , [Time].&[1998].&[Q3].&[8]         , [Time].&[1998].&[Q3].&[9]         , [Time].&[1998].&[Q4].&[10]         , [Time].&[1998].&[Q4].&[11]} on rows  from      [Sales_MDX2]

MDX Query

select     {[Measures].[Unit Sales]      , [Measures].[MA]} on columns     , {Descendants([Time].[Year].&[1997], [Month])} on rows from     [Sales_MDX2]

System DSN ODBC Connection Missing While Creating SSIS Connection Manager

We often come across this issue when our working server and source server have different bits 32 or 64.

You might have created System DSN in your system, but when try to create connection manager, the DSN will be missing in ODBC list. To overcome this you need to create DSN connection in appropriate ODBC (32/64).

Perform the followings:

Open command window:

1. Navigate to C:\Windows\Sysos64\Odbacd32.exe

2. ODBC Connection wizard will appear.

3. Create a new system DSN there.

4. Now try creating Connection Manager in SSIS package

Attaching MDF file to a Database

Below is the query to attach a .mdf file to a database

CREATE DATABASE AdventureWorks2008DWR2 ON

( FILENAME = N'C:\Users\mvaradhan\Downloads\AdventureWorksDW2008R2.mdf')

FOR ATTACH

GO

Query to get list of packages in a Integration Server

Below is the SQL query to get the list of packages deployed to an Integration Server:

WITH ChildFolders

AS

(

    SELECT PARENT.parentfolderid, PARENT.folderid, PARENT.foldername,

        CAST('' AS SYSNAME) AS RootFolder,

        CAST(PARENT.foldername AS VARCHAR(MAX)) AS FullPath,

        0 AS Lvl

    from msdb.dbo.sysssispackagefolders PARENT

    WHERE PARENT.parentfolderid IS NULL

    UNION ALL

    SELECT CHILD.parentfolderid, CHILD.folderid, CHILD.foldername,

        CASE ChildFolders.Lvl

            WHEN 0 THEN CHILD.foldername

            ELSE ChildFolders.RootFolder

        END AS RootFolder,

        CAST(ChildFolders.FullPath + '/' + CHILD.foldername AS VARCHAR(MAX))

            AS FullPath,

        ChildFolders.Lvl + 1 AS Lvl

    FROM msdb.dbo.sysssispackagefolders CHILD

        inner join ChildFolders ON ChildFolders.folderid = CHILD.parentfolderid

)

SELECT F.RootFolder, F.FullPath, P.name AS PackageName,

    P.[description] AS PackageDescription, P.packageformat, P.packagetype,

    P.vermajor, P.verminor, P.verbuild, P.vercomments,

    CAST(CAST(P.packagedata AS VARBINARY(MAX)) AS XML) AS PackageData

FROM ChildFolders F

    inner join msdb.dbo.sysssispackages P on P.folderid = F.folderid

ORDER BY F.FullPath ASC, P.name ASC;

Convert Seconds to hours, minute, seconds (hh:mm:ss) in MDX

WITH MEMBER [Measures].[TimSpent (HH:MM:SS)] AS '[Measures].[Timespent]/ 86400', FORMAT_STRING = "hh:mm:ss"

SELECT [Assignment].[Element].[Element] ON ROWS, {[Measures].[Timespent], [Measures].[TimSpent (HH:MM:SS)]} ON COLUMNS

FROM [Activity]

How to Fix Transaction Manager Connectivity Error in SSIS?

When we set Transaction Option to 'Required' in SSIS containers, often we get an error on Connection Manger

and data connection.

To work around this problem, follow these steps on the computer that Windows Server 2003 or Windows XP SP2 is installed on:

1.       Make sure that the Log On As account for the MSDTC service is the Network Service account. To do this, follow these steps:

a.       Click Start, and then click Run.

b.       In the Run dialog box, type Services.msc, and then click OK.

c.        In the Services window, locate the Distributed Transaction Coordinator service under Name in the right pane.

d.       Under the Log On As column, see whether the Log On As account is Network Service or Local System. 

If the Log On As account is Network Service, go to step 2. If the Log On As account is Local Systemaccount, continue with these steps.

e.       Click Start, and then click Run.

f.         In the Run dialog box, type cmd, and then click OK.

g.       At the command prompt, type Net stop msdtc to stop the MSDTC service.

h.       At the command prompt, type Msdtc –uninstall to remove MSDTC.

i.         At the command prompt, type regedit to open Registry Editor.

j.         In Registry Editor, locate the following key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC

registry key.

Delete this key.

k.       Quit Registry Editor.

l.         At the command prompt, type Msdtc –install to install MSDTC.

m.     At the command prompt, type Net start msdtc to start the MSDTC service.

Note that the Log On As account for the MSDTC service is set to Network Service account.

    Enable MSDTC to allow the network transaction. To do this, follow these steps:

 .         Click Start, and then click Run.

a.       In the Run dialog box, type dcomcnfg.exe, and then click OK.

b.       In the Component Services window, expand Component Services, expand Computers, and then expandMy Computer.

c.        Right-click My Computer, and then click Properties.

d.       In the My Computer Properties dialog box, click Security Configuration on the MSDTC tab.

e.       In the Security Configuration dialog box, click to select the Network DTC Access check box.

f.         To allow the distributed transaction to run on this computer from a remote computer, click to select theAllow Inbound check box.

g.       To allow the distributed transaction to run on a remote computer from this computer, click to select theAllow Outbound check box.

h.       Under the Transaction Manager Communication group, click to select the No Authentication Requiredoption. Set No Authentication Required on both the client and the remote systems.

i.         In the Security Configuration dialog box, click OK.

j.         In the My Computer Properties dialog box, click OK.

    Configure Windows Firewall to include the MSDTC program and to include port 135 as an exception. To do this, follow these steps:

 .         Click Start, and then click Run.

a.       In the Run dialog box, type Firewall.cpl, and then click OK

b.       In Control Panel, double-click Windows Firewall.

c.        In the Windows Firewall dialog box, click Add Program on the Exceptions tab.

d.       In the Add a Program dialog box, click the Browse button, and then locate the Msdtc.exe file. By default, the file is stored in the <Installation drive>:\Windows\System32 folder.

e.       In the Add a Program dialog box, click OK.

f.         In the Windows Firewall dialog box, click to select the msdtc option in the Programs and Services list.

g.       Click Add Port on the Exceptions tab.

h.       In the Add a Port dialog box, type 135 in the Port number text box, and then click to select the TCP option.

i.         In the Add a Port dialog box, type a name for the exception in the Name text box, and then click OK.

j.         In the Windows Firewall dialog box, select the name that you used for the exception in step j in thePrograms and Services list, and then click OK.

    Test pinging from the host server to the remote server, and from the remote server to the host server, using the netbios name (server name, without the domain). Microsoft Distributed Transaction Coordinator uses the netbios name, not the fully qualified domain name, to locate servers. If name resolution fails, distributed transactions will fail. If pings using the netbios name fails, refer to the following knowledge base article: