"Enable Visual Totals" in SSAS Security role

If you are filtering specific attribute members in SSAS and want the user to see the total of just the attribute members selected rather than all (even though they can only see the attributes you have allowed) then you must select "Enable Visual Totals".


If, at the all level, the user should see all total (including ones they can't see) then do not select the Enable Visual Totals.


If, at the all level, the user should only see the total for the members selected then select Enable Visual Totals.


E.g. Attribute A with 3 members with Measure X.


Attribute  | X Value
All           | 6     
M1           | 1     
M2           | 2      
M3           | 3        




If you grant the role access to M2 (2) and you do not have Enable Visual Totals select:


Attribute  | X Value
All            | 6          
M2           | 2           




If you grant the role access to M2 (2) and you have Enable Visual Totals select:


Attribute  | X Value
All           | 2         
M2           | 2      
          

Backing up Analysis Services

There's a very straight forward way to backup a cube onto a shared location.


First create the folder within the shared path e.g. \\SHAREDPATH\CUBEFOLDER


Open new XMLA window and  run XMLA code below on server with the cube



<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

<Object>

<DatabaseID>CUBENAME<DatabaseID>

</Object>

<File>\\SHAREDPATH\CUBEFOLDER\CUBENAME.abf</File>

<AllowOverwrite>true</AllowOverwrite>

</Backup>

SQL: xPath and xQuery

xPath and xQuery allow SQL to retrieve data from an xml source.



--XML xpath example
DECLARE @xml XML


SET @xml = N'



Gambardella, Matthew

Computer

44.95
2000-10-01
An in-depth look at creating applications
with XML.



Ralls, Kim
Fantasy
5.95
2000-12-16
A former architect battles corporate zombies,
an evil sorceress, and her own childhood to become queen
of the world.

';

SELECT x.books.value('@id[1]','varchar(10)') AS bookid
,x.books.value('title[1]','varchar(50)') AS bookName
,x.books.value('author[1]','varchar(50)') AS [BookAuth]
,x.books.value('description[1]','varchar(500)') AS [Desc]
,x.books.value('price[1]','money') as price
,x.books.value('publish_date[1]','datetime') as datePublished
FROM @xml.nodes('//catalog/book[price > 5]') as x(books)
where x.books.value('price[1]','money') > 6
order by bookid

--xQuery
SELECT @xml.query('
for $book in //catalog/book
where $book/price > 5
order by $book/title[1]
return $book')


SELECT @xml.query('
for $book in //catalog/book
where $book/price > 5
order by $book/title[1]
return
element Book
{(
element Title { data($book/title)},
element Author { data($book/author)}
)}
')


SELECT @xml.query('
for $book in //catalog/book
where $book/price > 5
order by $book/title[1]
return
element Book
{(
attribute Title { data($book/title)},
attribute Author { data($book/author)}
)}
')

ASP.NET: User Screen Resolution

Changing pages in .net depending on what resolution screen user is curently running:

SQL: Connecting to other servers

/*Add linked server*/

EXEC sp_addlinkedserver 'Server name', 'SQL Server'

GO