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!
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.
(SELECT SDS.name AS SharedDsName
,CONVERT(xml, CONVERT(varbinary(max), content)) AS DEF
FROM dbo.[Catalog] AS SDS
WHERE SDS.Type = 5) -- 5 = Shared Datasource
,DSN.value('ConnectString', 'varchar(150)') AS ConnString
SDS.DEF.nodes('/DataSourceDefinition') AS R(DSN)
) AS CON
-- Optional filter:
-- WHERE CON.ConnString LIKE '%Initial Catalog%=%TFS%'
ORDER BY 1 --, CON.[Path]
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
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
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
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.
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.
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:
- To provide a set of useful extensions to Analysis Services 2005 and higher.
- 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
// 2. clears the active Windows File System Cache
// 3. checks the size of the Windows File System Cache
// 4. clears the active and standby Windows File System Cache
// 5. checks the size of the Windows File System Cache
// 6. clears the Analysis Services cache for the current database using the ClearCache XMLA command internally
// 7. clears the Analysis Services cache, the active, and the standby Windows File System Cache
//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.DiscoverXmlMetadataFull("\Databases\Database", "State<>'Processed'" ,"<ObjectExpansion>ExpandObject</ObjectExpansion>")
// This is an example of a simple discover call
// This is an example passing in a CUBE_NAME restriction
// the available restrictions varies depending on the particular query.
// This is an example passing in a CUBE_NAME restriction
// and the CATALOG property
WHERE CUBE_NAME LIKE 'FAMI%'
ORDER BY DIMENSION_NAME, DIMENSION_ORDINAL");
There are a number of available tools that are free Continue reading
A few miscellaneous notes today on the location of various file when working with Analysis Services. Continue reading