--Use the queries below to pull in the DMV data into Power Pivot from your SSAS Server--
--You would pull these into four separate tables

---------------------------------------
--PERSPECTIVE--------------------------
---------------------------------------

Select [Cube_Name] as Perspective
  From $system.MDSchema_Cubes
  Where [Cube_Source] = 1 --Main user-defined perspectives only (& Model)
  
  
---------------------------------------
--DIMENSIONS---------------------------
---------------------------------------
Select
[Cube_Name] as [Perspective]
,[Dimension_Unique_Name] as [Table]
,[Description] as [TableDescription]
,[Dimension_Name] as [Field]
,[Description]
,[Dimension_Is_Visible]
From $system.MDSchema_Dimensions

---------------------------------------
--MEASURES-----------------------------
---------------------------------------
Select 
[Cube_Name] as Perspective
,[Dimension_Unique_Name] as [Table]
,[Hierarchy_Caption] as [Field]
,[Description]
,[Hierarchy_Is_Visible]
From $system.MDSchema_Hierarchies

---------------------------------------
--ATTRIBUTES---------------------------
---------------------------------------
Select 
[Cube_Name] as Perspective
,[MeasureGroup_Name] as [Table]
,[Measure_Caption] as [Field]
,[Description]
,[Measure_Is_Visible]
From $system.MDSchema_Measures