Tabular logging of Users is not like Multidimensional!

A few days ago, I wanted to start tracking on who was using my Tabular solutions. (If you wandered into this post… I’m talking about Microsoft’s SQL server Analysis Services.) With Multidimensional you have a table, that given the right circumstances, Look here: “SSAS not logging?” will track users of your Multidimensional solutions (think “cubes”…that’s what we used to call them). Well, Tabular has all the administrative set up as does Multidimensional… meaning right click on the tabular instance inside SSMS, and you’ll be given options wherein you could set up logging of users touching your Tabular solutions. WOW! GREAT! Just what i wanted…and just what i expected.

BUT! It didn’t work! And, it won’t work! Why? Because the Logging on Multidimensional models was originally conceived for the purpose of an optimization wizard…wherein, user queries would help guide what should be pre-aggregated. Well, Tabular, using Vertipaq doesn’t do any pre-aggregation. So, they have disabled that logging function. Yes, you CAN set it up. And yes, the table does get built in wherever you designate…but, NO it never gets filled in with details of users’ usage. Go figure!

Posted in SSAS 2012 | Leave a comment

SSRS Data Source Lister

Hello,

Sometimes, you’re in the middle of doing something, and you need an answer.  You google it and find such a great solution, you want to keep it handy for … whenever!  Here is such a post.  As the title of my post indicates, you can list out the Data Sources that exist on an SSRS server… and you can do so with this code…which i got entirely from http://gallery.technet.microsoft.com/scriptcenter/List-connection-strings-of-1a9a9adc.  Thank you Olaf Helper!

 

-- List connection strings of all SSRS Shared Datasources 
;WITH XMLNAMESPACES  -- XML namespace def must be the first in with clause. 
    (DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2006/03/reportdatasource' 
            ,'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' 
     AS rd) 
,SDS AS 
    (SELECT SDS.name AS SharedDsName 
           ,SDS.[Path] 
           ,CONVERT(xml, CONVERT(varbinary(max), content)) AS DEF 
     FROM dbo.[Catalog] AS SDS 
     WHERE SDS.Type = 5)     -- 5 = Shared Datasource 
 
SELECT CON.[Path] 
      ,CON.SharedDsName 
      ,CON.ConnString 
FROM 
    (SELECT SDS.[Path] 
           ,SDS.SharedDsName 
           ,DSN.value('ConnectString[1]', 'varchar(150)') AS ConnString 
     FROM SDS 
          CROSS APPLY  
          SDS.DEF.nodes('/DataSourceDefinition') AS R(DSN) 
     ) AS CON 
-- Optional filter: 
-- WHERE CON.ConnString LIKE '%Initial Catalog%=%TFS%' 
ORDER BY 1   --, CON.[Path] 
        ,CON.SharedDsName;

Posted in SQL 2005, SQL 2008, SSRS | 1 Comment

Reading an SSAS cube thru SQL using MDX

Do you need the ability to read a cube, but you want to read it thru SQL?  And, why would you do this?  Well, for example, suppose you wanted to email the results of a certain MDX query…and you didn’t want to set up SSRS to do this.  (SSRS would be a fine method, but you do have to set it up and create the report etc.)  Suppose you were simply more comfortable using a SQL Server Agent job to do this task for you.  Continue reading

Posted in SQL Administration, SSAS | Tagged , , , | Leave a comment

SSAS not logging?

So you’ve followed all the advice and set up logging so that you can know who is using your cubes.  Things are good.  One day you wake up and see that your logs no longer are logging.  You open your cube yourself and browse.  Then you re-check your logs…no entries.  You KNOW the cube is working.  You KNEW the log WAS logging.  Why not now?  Continue reading

Posted in SQL 2008, SSAS, SSRS | Tagged , | 1 Comment

Who’s been using your Cube?

To keep track of how many people are actually using the results of your hard work…
You have two considerations:

1. Turn on Logging on your Analysis Services Cube
2. Actually query that log… Continue reading

Posted in SQL 2008, SSAS | Tagged , , | Leave a comment

Sql Saturday Jacksonville, April 27 2013

Hey, everyone!  There’s a great event here in Jacksonville, Florida.  It’s coming in April.  It is like a mini Tech-ed.  Free Food.  Great Technical presentations.

BI Consultants toolkit

Click here to register!

Lots of networking.  Fellowship.  Swag.  After hours party.  What could be better than that? I will be speaking on a “BI Consultants Toolbelt”.  Free tools that any BI consultant should at least be aware of.  In fact they are the focus of my last two blog entries…plus they’ll be hilighted in the upcoming blog entries.

 

Posted in Career | Leave a comment

ASSP.dll…super great functionality

There is a “DLL” that you are going to want to install on your SSAS servers… it is called “ASSP.DLL”…and you can get it from Code plex.  To quote CodePlex:

The Analysis Services Stored Procedure project is a set of sample stored procedures for Analysis Services 2005, 2008, 2008 R2, and 2012. These samples have been written in C# and sample MDX queries are included that demonstrate the use of the procedures against the Adventure Works DW sample database. It was developed by a group of community volunteers with 2 main aims in mind:

  1. To provide a set of useful extensions to Analysis Services 2005 and higher.
  2. To provide a variety of example source code for people looking to write their own stored procedures.

So, go to CodePlex, follow their instructions and install the DLL on your server. After that is done, you can try the code below…

// 7 ASSP calls...

//  1.  checks the size of the Windows File System Cache
call ASSP.GetFileSystemCacheBytes()
//  2.  clears the active Windows File System Cache
call ASSP.ClearFileSystemCache(false)
//  3. checks the size of the Windows File System Cache
call ASSP.GetFileSystemCacheBytes()
//  4. clears the active and standby Windows File System Cache
call ASSP.ClearFileSystemCache()
//  5. checks the size of the Windows File System Cache
call ASSP.GetFileSystemCacheBytes()
//  6. clears the Analysis Services cache for the current database using the ClearCache XMLA command internally
call ASSP.ClearCache()
//  7. clears the Analysis Services cache, the active, and the standby Windows File System Cache
call ASSP.ClearAllCaches()

//But this returns the list without any context, If you want to report the CubeName and LastProcessed date you would put a vertical bar (|) after the Cube element in the path and then put a comma separated list of fields that you want returned
CALL ASSP.discoverXmlMetaData("\Database\Cubes\Cube|Name,LastProcessed\Dimensions\Dimension");

CALL ASSP.DiscoverXmlMetadata("\Database\Cubes\Cube\CubePermissions\CubePermission");
--CALL ASSP.DiscoverXmlMetadata("\Database\Cubes\Cube\CubePermissions\CubePermission\DimensionPermissions\DimensionPermission\AttributePermissions\AttributePermission");

--CALL ASSP.DiscoverXmlMetadataFull("\Databases\Database", "State<>'Processed'" ,"<ObjectExpansion>ExpandObject</ObjectExpansion>")

CALL ASSP.DiscoverXmlMetadata("Member");
------------------------------------------------------------------------

// This is an example of a simple discover call
CALL ASSP.Discover("DBSCHEMA_CATALOGS")

// This is an example passing in a CUBE_NAME restriction
// the available restrictions varies depending on the particular query.
CALL ASSP.Discover("MDSCHEMA_DIMENSIONS","<CUBE_NAME>FAMIS3</CUBE_NAME>")

// This is an example passing in a CUBE_NAME restriction
// and the CATALOG property
CALL ASSP.Discover("MDSCHEMA_DIMENSIONS","<CUBE_NAME>FAMIS3</CUBE_NAME>","<CATALOG>FAMIS</CATALOG>")

CALL ASSP.DMV("
SELECT DIMENSION_ORDINAL
 , [DIMENSION_NAME]
 , DIMENSION_CARDINALITY
 , DEFAULT_HIERARCHY
  FROM $SYSTEM.MDSCHEMA_DIMENSIONS
 WHERE CUBE_NAME LIKE 'FAMI%'
ORDER BY DIMENSION_NAME, DIMENSION_ORDINAL");

Posted in SSAS | Tagged , , | 1 Comment

Tools to help you when developing SSAS cubes

There are a number of available tools that are free Continue reading

Posted in SSAS | Leave a comment

SSRS subscriptions not working

  1. The problem: SSRS Subscription is not sending out emails.
  2. Where to find a typical error-log: C:\SQLServer\MSRS10_50.MSSQLSERVER\Reporting Services\LogFiles Continue reading
Posted in SSRS | 2 Comments

SSAS File organization

A few miscellaneous notes today on the location of various file when working with Analysis Services. Continue reading

Posted in SSAS | 1 Comment