Some question I see popping up every now and then is “I have configured a Tabular model on SQL 2012 and also installed SharePoint 2010 with SP1 but I am able to view “BI Semantic Model Connection File” file in SharePoint. What is happening ?”
The problem is usually pretty easy to fix: You can go through the steps mentioned here: http://blogs.msdn.com/b/karang/archive/2012/01/27/add-a-bi-semantic-model-connection-content-type-to-a-library-powerpivot-for-sharepoint.aspx . If even so, you are not able to see them, try to activate the PowerPivot features in your site collection.
A quick post that proved helpful to me today. I needed to see the number of rows for all tables in the database. I found this query:
SELECT ‘[' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + ']‘ AS fulltable_name, SCHEMA_NAME(t.schema_id) AS schema_name, t.name AS table_name,
i.rows
FROM sys.tables AS t INNER JOIN
sys.sysindexes AS i ON t.object_id = i.id AND i.indid < 2
on Brian Knights blog: Quickly Retrieving the Row Count for All Tables. One thing to note is that the numbers could be a little out of date. DBCC UPDATEUSAGE (DBNAME) should resolve that, the problem is that this only works when you are admin. If you want to be 100% accurate you might need to do a little more investigation.
As part of the SQL Server 2012 release Microsoft has released sessions on the Microsoft Virtual Academy one of the is sessions is 7 Module session on Breakthrough Insights using Microsoft SQL Server 2012 – Analysis
One of the session there is one I did together with my colleague Howie Dickerman called: Enriching your BI Semantic Models with Data Analysis Expressions. This will give you a basic understanding of DAX and will go through the new features of DAX for SQL Server 2012.
Enjoy !
As of today you can download a evaluation version SQL Server 2012. For purchasing SQL Server 2012 you have to wait until April first. For more information check out the official blog post: SQL Server 2012 Released
This is the first SQL Server release I am proud to be part of, the AS team has delivered tons of new features in the BISM, tabular projects, Power View integration, more DAX and of course a new version of the PowerPivot add-in. I think this is great release for BI with Power View now added to the stack as great interactive data exploration, visualization, and presentation experience.
This release also includes the long awaited 64 bits version of the Data mining add-in, which you can download here.
PowerPivot for SQL Server 2012 is now here as well, you can download it here.
With the release of SQL Server 2012 tabular models identifying what the memory use of objects on your server instance is has become more important then ever. Since everything is in memory, being able to tune the model to remove columns that take up loads of memory could be very valuable. Or being able to use your development database to extrapolate the memory usage that you will have on you product machine.
I have created a PowerPivot workbook that will allow you to investigate the memory usage on your server instance (this report could also be used on a MOLAP instance).
The report contains two worksheets. Worksheet 1 contains two dashboards, the first dashboard contains the top 10 tables on the server instance by memory usage.
The second worksheet allows a more detailed investigation on all the objects on the server by using a pivottable. I have created a hierarchy of all the objects that allows to start at the top to bottom when you want to investigate the details.
of course since this is a pivottable there you can create your own insights if you want:
You can download the workbook and try for you self here BISMServerMemoryReport.
In order to get this working on your tabular or multidimensional server is change the connection:
After this, refresh the table and refresh the data in the workbooks from Excel.
The data itself is being retrieved into one table by a DMV function:
Then the data that came in was manipulated by several DAX calculated columns using new SQL 2012 DAX functions like parent child functions and created hierarchies for them.
I hope you can use this workbook, let me know if you have any comments or feedback on the report. I am always looking for new insights and ways to improve the workbook.
Are you new to data modeling in PowerPivot or SQL Server Data Tool (aka BIDS) or never had the chance to start with DAX, now is the time. We have released online content to help you get started in creating DAX formulas. You can think of this as DAX 101.
The QuickStart: Learn DAX Basics in 30 Minutes is now available as part of the DAX Resource Center Wiki.
Leave a comment if you have some feedback, its a online Wiki so we can change it 
.
An updated version of the whitepaper “Data Analysis Expressions (DAX) In the Tabular BI Semantic Model” is available for download at http://go.microsoft.com/fwlink/?LinkID=237472&clcid=0×409.
The Whitepaper is now updated with SQL Server 2012 DAX functions and well worth the read if you are interested in DAX (which you no doubt are if you are following my blog). Thanks to Peter Meyers and Howie Dickerman for writing this whitepaper and Owen Duncan for contributing to it.
