Category Archives: Analysis Services

DAX: Get the Month Name from a Date

Some commonly asked questions in this world are :

1. Is the Loch Ness monster real ?

2. Is the Bermuda Triangle really aliens ?

3. How do you get the month name from a date in a PowerPivot DAX formula ?

Get the three character abbreviation of the month name from a Date column in Powerpivot using the following derived column formula…

=FORMAT(MONTH([YOURDATECOLUMN]),”MMM”)

month

 

Data Wow: Chances of Finding Love ?

coffee

twolove

What are the chances of life on earth ? Further, what are the chances of running into a random stranger on earth, and finding love… And further again, what are the chances of holding that love ??   In this compelling Ted talk, mathematician Hannah Fry gives her top 3 mathematically based tips for finding that special 1.

Tableau: Couldn’t find valid MS PowerPivot data

You have attempted to connect Tableau desktop to your PowerPivot data source and encounter the error “the drivers necessary are not installed” or  “couldn’t find valid MS PowerPivot data”.

The following instructional video demonstrates how to solve both of these issues and get you running…

http://bicg.com.au/index.php/connecting-to-your-power-pivot-data-from-tableau/

IMG_20140712_054549_1

SSAS: Access Denied – The file specified in the restore command is damaged or is not an AS backup file

You have attempted a database restore in Analysis Services from a .abf file and get the error : “The file specified in the restore command is damaged or is not an AS backup file.  The following system error occurred : Access is denied”.   The key clue in the error is the last part.

accessdenied

Go to the actual file, i.e. not the folder, right click, select properties, the click the Security tab.  Edit the file’s security so that the Analysis Services service account has sufficient permissions i.e. full permissions will work.  If you don’t know the Analysis Services Services account, go to SQL Server Configuration Manager and find your Analysis Services service where you will see the service account.

MDX: How to Pass a SSRS Parameter Label into a MDX Parameter

Ok Computer, Sing… us a song …

You have to pass a text string from your Reporting Services parameter (Label) into a MDX dataset as a parameter. An example MDX dataset is outlined here …


SELECT Measures.[some stuff]

ON 0,

FROM [Your Model]

WHERE

(

StrToSet("[Dimension].[Attribute].&[" + @YourString + "]")

)

DAX: NAAN is good…NAN is not…use DIVIDE

Just a minute while I reinvent myself …

Measure [ExampleWithoutDivide] divides one measure by another in your Tabular cube…and when you run a query in Management studio you get -1.#IND values …

Screen Shot 2014-06-18 at 1.31.29 pm

And in your Reporting Services dataset you get NaN values …

Screen Shot 2014-06-18 at 1.32.51 pm

Use DIVIDE in your MDX calculation as illustrated in the [ExampleWithDivide] measure above…

ExampleWithDivide:=DIVIDE([Measure1],[Measure2])

Oe Use an alternative result option to return a constant numeric value instead …

Screen Shot 2014-06-18 at 1.50.04 pm

ExampleWithDivide:=DIVIDE([Measure1],[Measure2],1)

See you in places
You’ll be upstairs…
And I’ll be there too…

PowerPivot for Sharepoint – Locating Analysis Services on a Different Server

PowerPivot for Sharepoint 2013 supports a variety of different installation architectures – one of which allows you to run the PowerPivot Analysis Services instance on a server other than your Sharepoint 2013 server.   You still need to install PowerPivot software on your Sharepoint server, however only the spPowerpivot.msi package (or on all Sharepoint servers if you have a multi node Sharepoint farm) – this can be found on your SQL Server install media or located on the SQL 2012 feature pack http://go.microsoft.com/fwlink/p/?LinkID=263965.   Make sure the same version of msPowerPivot.msi is used on all servers in farm.

Make sure you specify the Excel Services Data Model settings in the configuration of your Excel Services Service Application – this is the instance name of your PowerPivot instance e.g. HOSTNAME\POWERPIVOT.

For Kerberos configuration you will need to get the Port number of your POWERPIVOT instance (as well as the port number of the browser service), create your MSOLAPsvc.3 and MSOLAPDISCO.v3 SPNs then add these to your constrained delegation configuration.

Finally, you need to run the PowerPivot configuration tool to complete your installation – because you installed msPowerPivot.msi on the Sharepoint server, you can run the PowerPivot configuration tool and finish the PowerPivot integration.

Screen Shot 2014-01-19 at 7.08.40 am