Search this blog

Monday, June 27, 2016

DMV Queries: To Get All Attributes in a cube

Use below Dynamic Management View (DMV) queries to get all the dimension and attributes of your cube

SELECT
       [CATALOG_NAME] as [Database],
       CUBE_NAME AS [Cube],
       [DIMENSION_UNIQUE_NAME] AS [Dimension],
       HIERARCHY_DISPLAY_FOLDER AS [FOLDER],
       HIERARCHY_CAPTION AS [DIMENSION ATTRIBUTE],
       HIERARCHY_IS_VISIBLE AS [VISIBLE]
FROM
       $system.MDSchema_hierarchies
WHERE 
       CUBE_NAME  ='My Cube Name' AND 
       HIERARCHY_ORIGIN=2
ORDER BY
       [DIMENSION_UNIQUE_NAME]
 
Note: Replace 'My Cube Name' with your cube name

Wednesday, June 22, 2016

Fix: Unable to create the type with the name 'SPCRED'

Problem:
I tried to export one of the SSIS packages from Integration Services catalog. I got the below error
 
"Unable to create the type with the name 'SPCRED'. (Microsoft.SqlServer.ManagedDTS)"
 
Solution:
When I explore this issue, found that the package extracts data from SharePoint list. but we don't have inbuilt component in SSIS to fetch the data from SharePoint. So We need to use third party connecter which is exists in CodePlex "SharePoint List Source and Destination"
 
 
after Install this connector, I can export the  SSIS packages from Integration Services catalog without any issues.
 
Also, you can see this connector added in SSIS connection Manger
 
Note:
  • If it is not appeared in connection manager then you've to restart your Visual studio if it opened
  • If not appeared in connection Manager after restart your VS, then the version you installed is not compatible with your VS, so choose the Proper version and install it.