Just a quick post here about a situation that came up with literal dates in DAX when a client downloaded one of my DAX for T-SQL folks scripts and found that it didn't work. The statement is this:
evaluate (
FILTER('Date','Date'[Full Date] = "2013-04-01")
)
You receive an error stating a data type mismatch and the above doesn't run.
The problem is with the compatibility mode your BISM Tabular model is in. In 11.0.0 the above syntax works fine but with 11.0.3 you get the mismatch error.
The statement above needs to use the FORMAT function with 11.0.3 as below:
evaluate (
FILTER('Date', FORMAT('Date'[Full Date],"YYYY-MM-DD") = "2013-04-01")
)
Hope this helps and included the actual code below to download.
Thursday, May 16, 2013
Monday, May 6, 2013
Measures, Non-Measures, or a Combo in Power View
If you are new to using Power View you may notice that there are images on the left of the available fields in the Field List:
When you are creating your visualizations it is important to know what numbers you can see and what they can be sliced by: E.g. Product Profit by Sales Number.
Numbers are Measures represented by the Calculator. E.g. Product Cost & Product Profit above.
Slicers are Non-Measures represented by no image. E.g. Sales Category above.
Combo is represented by the greek letter Sigma. E.g. SalesNumber above.
So lets drag them into a blank Power View report to demonstrate the above.
Product Profit sliced by Sales Category and Count of SalesNumber.
When using the combo field SalesNumber I chose to a Distinct Count of SalesNumber to show SalesNumber as a Measure to answer the business question how many Sales were attained. This can be done in the Layout Section which is directly below the Fields List section in Power View.
.
To change the combo field SalesNumber from a Measure to a Non-Measure or Slicer, simply select the "Do Not Summarize" option and you will notice it change in Power View as below to answer how much profit per transaction was attained:
I will be explaining this in further detail and covering a lot of other material about Power View this Wednesday night May 8, 2013 at the MSBI South Florida User Group event at 6 pm. More info on the event is here and hope to see you all there!
When you are creating your visualizations it is important to know what numbers you can see and what they can be sliced by: E.g. Product Profit by Sales Number.
Numbers are Measures represented by the Calculator. E.g. Product Cost & Product Profit above.
Slicers are Non-Measures represented by no image. E.g. Sales Category above.
Combo is represented by the greek letter Sigma. E.g. SalesNumber above.
So lets drag them into a blank Power View report to demonstrate the above.
Product Profit sliced by Sales Category and Count of SalesNumber.
When using the combo field SalesNumber I chose to a Distinct Count of SalesNumber to show SalesNumber as a Measure to answer the business question how many Sales were attained. This can be done in the Layout Section which is directly below the Fields List section in Power View.
To change the combo field SalesNumber from a Measure to a Non-Measure or Slicer, simply select the "Do Not Summarize" option and you will notice it change in Power View as below to answer how much profit per transaction was attained:
I will be explaining this in further detail and covering a lot of other material about Power View this Wednesday night May 8, 2013 at the MSBI South Florida User Group event at 6 pm. More info on the event is here and hope to see you all there!
Thursday, March 28, 2013
Slides Uploaded from recent Speaking Events: SFSSUG, MSBISF, Pragmatic Works
I know its a been awhile, and know a lot of folks have asked so here are the decks from my recent speaking engagements. I have also realized I need a new template for presentations :-) It's added to my list!
South Florida SQL Server User Group (SFSSUG) - Conquer Reporting by Scaling Out SQL Server
Microsoft BI User Group of South Florida (MSBISF) - SSAS Tabular and Self Service BI - The DynamDAX Duo
Pragmatic Works Free Training on the T's - Self Service Visualization with Power View
South Florida SQL Server User Group (SFSSUG) - Conquer Reporting by Scaling Out SQL Server
Microsoft BI User Group of South Florida (MSBISF) - SSAS Tabular and Self Service BI - The DynamDAX Duo
Pragmatic Works Free Training on the T's - Self Service Visualization with Power View
Thursday, February 28, 2013
Creating Images in Your Power View Visualizations
Thank you for all of the nice emails, direct messages on twitter from the webinar I gave on Power View as part of Pragmatic Works Free Training on the T's on Valentine's Day. And yes I did play Mr. Barry White to get you in "the mood" for some Power View awesomeness. You might see a lot of other Power View presentations, but doubt Barry White will be part of those!
A lot of you also asked about the process to add images to your BISM Tabular or PowerPivot model so figured I would write it up. It can be done in 5 simple steps.
I added images to the Adventure Works 2012 Data Warehouse database on DimProductSubCategory
1. Alter the DimProductSubCategory table and add a column to store the URL.
2. Create a folder on the SharePoint site to store the images you wish to associate to the product sub categories. You don't have to use SharePoint as your location but should make sure the permissions will work for your chosen URL location.
3. Upload the images to the SharePoint folder or your chosen URL Location.
4. Update the DimProductSubCategory table in the database with the URL for each SubCategoryPicture. Now I named each image exactly as the EnglishProductSubCategoryName that you find in the table so I could easily produce a URL for all of the product sub-categories with a simple update statement.
5. I used a BISM Tabular Model as my source for Power View so I just need to import the new column from its underlying SQL Server Database into the BISM Tabular model. This can easily be done by selecting Table > Table Properties > Check the box for the new column SubCategoryPicture.
On the SubCategoryPicture column set Image URL properties to true. Save Your Model and Deploy it.
Once Deployed you can embed images in your Power View Visualization like below:
Hope this helps you create images in Power View easily!
A lot of you also asked about the process to add images to your BISM Tabular or PowerPivot model so figured I would write it up. It can be done in 5 simple steps.
I added images to the Adventure Works 2012 Data Warehouse database on DimProductSubCategory
1. Alter the DimProductSubCategory table and add a column to store the URL.
2. Create a folder on the SharePoint site to store the images you wish to associate to the product sub categories. You don't have to use SharePoint as your location but should make sure the permissions will work for your chosen URL location.
3. Upload the images to the SharePoint folder or your chosen URL Location.
4. Update the DimProductSubCategory table in the database with the URL for each SubCategoryPicture. Now I named each image exactly as the EnglishProductSubCategoryName that you find in the table so I could easily produce a URL for all of the product sub-categories with a simple update statement.
5. I used a BISM Tabular Model as my source for Power View so I just need to import the new column from its underlying SQL Server Database into the BISM Tabular model. This can easily be done by selecting Table > Table Properties > Check the box for the new column SubCategoryPicture.
On the SubCategoryPicture column set Image URL properties to true. Save Your Model and Deploy it.
Once Deployed you can embed images in your Power View Visualization like below:
Hope this helps you create images in Power View easily!
Friday, February 8, 2013
Fall In Love with Power View this Valentine's Day
Fall in love with Power View this Valentine's Day. Thursday February 14, 2013 at 11 am est I am presenting Self-Service Visualization with Power View as part of Pragmatic Work's Free Training on the T's (Tuesdays and Thursdays).
In this session I will give a brief history of Self-Service Visualization and some tools out there today that facilitate it. I will then introduce Power View and the two ways it can be used; SharePoint 2010 (or 2013) and now Excel 2013. I will then cover what sources can be used to create a Power View report then jump into a demo and create a few visualizations from a blank canvas to demonstrate how easy, yet powerful this visualization tool really is.
Hope you can attend! You can register by clicking here.
In this session I will give a brief history of Self-Service Visualization and some tools out there today that facilitate it. I will then introduce Power View and the two ways it can be used; SharePoint 2010 (or 2013) and now Excel 2013. I will then cover what sources can be used to create a Power View report then jump into a demo and create a few visualizations from a blank canvas to demonstrate how easy, yet powerful this visualization tool really is.
Hope you can attend! You can register by clicking here.
Monday, January 14, 2013
Presenting Scaling Out SQL Server to South Florida SQL Server User Group This Wednesday
This Wednesday night 1/16/2013 in Doral, FL I am presenting Scaling Out SQL Server with Replication to the South Florida SQL Server User Group.
If you want to learn SQL Server Replication basics from the ground up don't miss this event! I will outline a two tier application architecture then show how splitting reporting out can be done using replication with near real time performance. I will walk the group through how to configure replication from the ground up in a demo environment, and cover best practices and explain all the essential pieces of replication to set you up for success.
More information on the event can be found here.
If you want to learn SQL Server Replication basics from the ground up don't miss this event! I will outline a two tier application architecture then show how splitting reporting out can be done using replication with near real time performance. I will walk the group through how to configure replication from the ground up in a demo environment, and cover best practices and explain all the essential pieces of replication to set you up for success.
More information on the event can be found here.
Thursday, January 10, 2013
SSAS Tabular and Self-Service BI Webinar Available
Just a quick post to let everyone know that the webinar I did on SSAS Tabular and Self-Service BI as part of Pragmatic Works free training on the T's on December 20, 2012 is available for viewing via their website here.
This webinar is almost the same presentation I gave at the Microsoft Business Intelligence User Group of South Florida meeting last night minus some DAX code and Power View visualizations.
This webinar is almost the same presentation I gave at the Microsoft Business Intelligence User Group of South Florida meeting last night minus some DAX code and Power View visualizations.
Subscribe to:
Posts (Atom)