Azure Analysis Services (AAS) to Power BI Premium (Fabric) Migration Experience now GA!

So back in December I did an initial post about the Azure Analysis Services (AAS) Migration to Power BI Premium Experience and then in February I did a follow up Power BI Report Source Redirection after AAS to PBI Migration to provide more details about the report redirection options. Well I am happy to say that last week during the Microsoft Build event this feature has now gone Generally Available (GA) as announced here in the May 2023 Feature Summary post!

You might be wondering if anything changed and the answer is yes! As part of the process there is now an automated way to handle the Power BI report dataset rebind process after the migration (this does not include Paginated reports). In the February post I showed how you can leverage the API calls and do this manually, well now there is a simple menu option that will do it for you as mentioned in the May post:)

This new rebind report option is available in the AAS database migration details area from the database context menu like shown below

Here you will see three options – Rebind reports, Undo rebind, and Rebind status.

In my example here for the demo after my AAS database migration to a Power BI dataset I have four copies of the report that are in different workspaces – the migration workspace AASMigration, My Workspace, Demo Workspace, and Demo Pro Workspace. Two of the workspaces are Premium – AASMigration and Demo Workspace – and then two are not – My Workspace and Demo Pro Workspace. What we will see is that the new “Rebind reports” process can take care of all reports referencing the existing AAS database across the entire tenant, even workspaces that I might not have permissions to.

Before checking out the new menu options let’s first look at the reports referencing the AdventureWorksAAS_PowerBIRpt dataset that has the Live Connection to the AAS database.

We can see above that there are currently four reports. Now let’s go ahead and test out the new menu options.

When I select the “Rebind reports” option I will get the below pop out panel message (no details since this is the first time I have selected the option).

After a short period of time if I select the “Rebind status” menu option I will see the following –

If I look at the lineage view for the dataset we will see that there are four reports in the four different workspaces now associated with the Power BI dataset that was migrated over from AAS.

I can then go into one of the reports in any of the workspaces and see that the report is working as expected as shown here by opening the report in the “Demo Pro Workspace” that is referencing the dataset in the “AASMigration workspace”. This is using a Pro/Shared workspace going against the dataset that is in a Premium workspace.

If you were just testing out the rebind process or the database migration process you could then go ahead and use the “Undo rebind” process and that will switch the dataset related reports back to the AAS database.

Note: after you run the rebind process it can take a minute to propagate the permissions, so if you try and click on a report immediately after you run the process you might get the following message as shown below – “Permission required – You cannot see the content of this report because you do not have permissions to the underlying dataset. Please contact the dataset owner to request access.” Just be patient and what a minute or two for the permissions to be updated with the source being swapped out within the report.

So that is it for the new Power BI report rebind automation process, pretty cool and saves a lot of time if you have a lot of reports. The nice option is if for some reason you do the rebind and there is an issue you can always use the undo option and revert back;)

So what do you think? Have you tried the AAS migration process yet? If so I would love to hear your feedback and experience, please leave a comment below!

AAS Migration resources –

• Azure AS migration main page: https://aka.ms/AASMigration
• Azure AS migration guide: https://aka.ms/AASMigrationGuide
• Azure AS migration scenarios: https://aka.ms/AASMigrationScenarios

Power BI Report Source Redirection after AAS to PBI Migration

This is a follow up to the post Azure Analysis Services (AAS) Migration to Power BI Premium Experience that I did back in December. In that post I mentioned the following towards the end of the post –

If you have existing Power BI reports using the AAS live connection you are able to use the report rebind API to point the report to use the Power BI dataset. The one thing to note as pointed out in the documentation is that as of now if you need to modify the report layout you would need to do that directly in the service. That portion of the migration process is still being worked on and hopefully should be updated to resolve the issue so that you will be able to download the redirected report from the service, modify it, and then publish the report back to the Power BI service.

In this post I want to touch on this a bit more and provide some follow up based on work that has taken place since that post on the migration rebind (redirection) process.

In my Power BI workspace I have two reports that are connected to my original AAS database used in the migration process, one is a Power BI report and the other is a Paginated (Operational) report.

If we look at the lineage view we can see that both reports are referencing Analysis Services –

I have already gone through the AAS to PBI Premium migration and moved the database from Azure Analysis Services to the Power BI Premium workspace shown above and it is named ‘AdventureWorksLTAAS’ as shown below in the screenshot.

Now what we can do is use the APIs to redirect both reports from using AAS so that they use the new Power BI dataset. Since I have to different types of reports I will be using two different APIs and we will start with the Power BI Report. To do this I will open up Windows PowerShell ISE as Administrator, login to Power BI, and then call the Report Rebind in Group API. Below is the outline of the steps to use and I included the install of the Power BI modules as well. You would need to include the values of the new dataset, workspace, and report and remove the square brackets.

#STEP 1: run PowerShell as Administrator

#STEP 2: run script if you don't have this module installed, might get prompted to install library
If (!(Get-module MicrosoftPowerBIMgmt)) {
Install-Module -Name MicrosoftPowerBIMgmt
}

#STEP 3: login to service (you will get prompted)
Login-PowerBIServiceAccount

#STEP 4: rebind report to Power BI dataset (new model)
$body = @{ "datasetId" = "[new dataset id]" }
Invoke-PowerBIRestMethod -Method Post -Url "https://api.powerbi.com/v1.0/myorg/groups/[workspace id]/reports/[report id]/Rebind" -Body ($body|ConvertTo-Json)

Once this is completed you will see that the lineage view has changed for the Power BI report so it is now referencing the Power BI dataset that was created as a result of the AAS database migration.

Now we will do a similar process with the Paginated report, but for this you need to use the Update Datasources in Group API since this is an RDL report file. Below is the code used for this in PowerShell and you would need to provide the corresponding values for the datasource name, workspace XMLA endpoint, dataset name, workspace, and report and remove the square brackets.

#STEP 1: run PowerShell as Administrator

#STEP 2: run script if you don't have this module installed, might get prompted to install library
If (!(Get-module MicrosoftPowerBIMgmt)) {
Install-Module -Name MicrosoftPowerBIMgmt
}

#STEP 3: login to service (you will get prompted)
Login-PowerBIServiceAccount

#STEP 4: rebind report datasource to Power BI dataset (new model)
$body = '{
"updateDetails":[
{
"datasourceName": "[datasource name]",
"connectionDetails": {
"server": "[workspace xmla endpoint]",
"database": "[dataset name]"
}
}
]
}'

Invoke-PowerBIRestMethod -Method Post -Url "https://api.powerbi.com/v1.0/myorg/groups/[workspace id]/reports/[report id]/Default.UpdateDatasources" -Body $body

The one thing to note about this process for the Paginated report is that the actual RDL file doesn’t get modified. So if you were to download the file or use the option in the service to “Edit in Report Builder” you would still see the AAS information. If you view the lineage though you will see that it is referencing the Power BI workspace now via the XMLA endpoint as shown below.

So an alternate option if you actually want the Paginated report file definition updated would be to use the “Edit in Report Builder” option and change the actual information or update the file that you might have in source control and simply republish.

Now the final thing I want to wrap this post up with is the issue regarding modifying the Power BI report files as I mentioned in my previous post as well as which is highlighted in the “Import Considerations” section of the AAS migration documentation.

During preview, if you use the Reports – Rebind Report Power BI REST API to create a new binding to the migrated dataset, you can’t make changes to reports in a Power BI Desktop .pbix that were previously bound to the model in Azure Analysis Services and then republish to Power BI. Report changes for a migrated dataset can be made in the Power BI service.

What you will see if you download the Power BI report file from the service, modify it, and then try to publish it back to the workspace is the following message.

This is the item that was being worked on and I am happy to announce an update is currently being deployed to resolve this. This update is in the process of being deployed to the service so it will take some time to make it available in all regions, but once it is what you will be able to do is use the “Upload” option in the workspace to get your updated file available.

There is of course still the option to simply update the files directly in the service as mentioned. The other fix that is coming and will be available in the March Power BI Desktop release is to resolve the dialog message above “Couldn’t publish to Power BI” when you publish.

With the March release you will see the following message when you publish your report that you downloaded after the report rebind and want to publish changes back to the service.

That is what we like to see:)

So I would be curious, has anyone been using the AAS to Power BI Premium migration process? If so, what has your experience been and what kind of feedback do you have? Feel free to leave a comment, I would be very curious to know more about your journey!

Azure Analysis Services (AAS) Migration to Power BI Premium Experience

Over the past month couple of months I got the opportunity to test out the new migration experience that was just made available for Public Preview this past month during the PASS Data Community Summit and announced on the Power BI blog here Accelerate your migration experience from Azure Analysis Services to Power BI Premium with the automated migration tool. The blog post also shows a very quick animated gif walkthrough of the process and there is a thirteen minute video from the MS Build conference earlier this year where this was first demoed that you can check out here as well The Future of Enterprise Semantic Models.

Being able to publish Analysis Services databases to Power BI Premium has been available to do previously, but this feature makes the option easier. In order to use this feature make sure you read over the prerequisites and information about the migration experience (I highly recommend reviewing this prior to actually attempting to do a migration) Migrate Azure Analysis Services to Power BI. I also got to help review and provide feedback about this documentation as well, so if anything appears to be missing or needs more explanation please use the Feedback option at the bottom of the page.

Some key items that I like about the new experience versus simply doing this manually on your own is the following:

• Automates the process of backing up AAS database, copying the backup to ADLS Power BI storage, and then restoring the database which becomes the Power BI dataset
• This will not only migrate 1500 compatibility mode models but also 1200 without any issues
• It will migrate the roles and permissions over, so database Admins will have write permissions on the dataset and other users will have build permissions
• The other item once you have migrated the databases over is the ability to enable the AAS server redirection

AAS server redirection, what does that do you? Well, let’s say that you have users that might have created a bunch of PivotTable reports in Excel against the AAS database. As part of the migration experience you will now be able to simply redirect all of these users to the Power BI dataset and they won’t have to make any changes in their Excel file connections or even have to know that anything changed. All with a click of a button! Wow, pretty cool huh? And once the server redirection is enabled you can actually pause the AAS service and all external client applications, tools, and automated processes will simply get redirected to the Power BI dataset.

If the redirection is not working refer to the documentation link for the client library minimum requirements, initially the redirection wasn’t working with my click-to-run (C2R) install of Excel but with a recent update this past month it now has the updated msolap library:) Actually I ran into an issue with my Office install and to resolve the issue the online repair uninstalled and reinstalled my Office which not only fixed my issue but also allowed me to go with the 64bit version of Office and provide the updated Excel and msolap library;) For Excel (at least the C2R version) you can find the msolap version in the following location –> %programfiles%\Microsoft Office\root\vfs\ProgramFilesX86\Microsoft Analysis Services\AS OLEDB\140

I won’t go over all of the steps of the migration process since those are covered in the documentation as well as shown in the video links above but if you try and create a new migration and get the following message “You don’t have permission to access to the server” as shown below it is most likely that you don’t have owner or contributor access to the AAS service in Azure, so verify that.

And after you create the migration in the Power BI service it might not be obvious but to begin the process you need to click on the AAS server name to review the configuration and to start the database migration process.

Once you access the migration you need to make sure that the AAS server is started and running, otherwise if it is paused you will get the following error message

On the migration page details screen you will see all of the configuration information for the AAS server as well as the Premium workspace and a list of the databases that are available to migrate.

If something is not configured properly as per the prerequisites you will get a warning message letting you know like if you don’t have storage configured for either the AAS server or workspace. This is a requirement for the backup and restore process to complete successfully.

If you try to migrate an AAS database that is larger than the Power BI Premium capacity will allow you will get a message like the following during the attempt to restore the backup file to the workspace. In this case I am trying to restore a database that is larger than 3GB to an A1 SKU which has a 3GB limit and this fails. So make sure that the capacity you are migrating to will support the databases you want to move. You can see the details and progress of the migration if you click on the database name when the migration process starts.

Currently a blank dataset will be created after the above failure in the workspace and it won’t contain any metadata – something that should probably be cleaned up and is a bit unexpected.

If the database does successfully restore then you will be able to start using it to create reports off of and once everything is migrated over you can enable the server redirection so that all of the external connections will start using the Power BI datasets and then you can pause the AAS server.

Once the AAS server is paused after the redirection is enabled you will see that the Excel PivotTables will still continue to work without having to make any changes to the connection string information.

Now that redirection is enabled I pause the AAS server and then change the slicer selection in the Excel PivotTable and the report still works as expected. You might get prompted for authentication and then the other thing you will see is if you are using sensitivity labels in Power BI that the file will inherit that as well.

If you have existing Power BI reports using the AAS live connection you are able to use the report rebind API to point the report to use the Power BI dataset. The one thing to note as pointed out in the documentation is that as of now if you need to modify the report layout you would need to do that directly in the service. That portion of the migration process is still being worked on and hopefully should be updated to resolve the issue so that you will be able to download the redirected report from the service, modify it, and then publish the report back to the Power BI service.

If you are currently using SSAS or AAS and haven’t moved your models to Power BI Premium I would definitely try it out and this new migration experience definitely simplifies the process. Please provide feedback and let us know about your experience.

Resources:

• Blog post – Accelerate your migration experience from Azure Analysis Services to Power BI Premium with the automated migration tool
• Documentation – https://aka.ms/AASMigration
• Video – The Future of Enterprise Semantic Models
• Guidance documentation – https://aka.ms/AASMigrationGuide (provides feature comparison between AAS and Premium)
• Scenario documentation – https://aka.ms/AASMigrationScenarios

Power BI Data Driven Subscriptions with Power Automate (Report Bursting)

Being able to do a data driven report subscription with Power BI and Paginated reports is a common request we hear from many customers. Let’s say you want to send a PDF version of a report to each of your store or department managers using a set of parameter values specific to each person. In the Power BI service that is not an option, but using Power Automate you can do this.

In this post I will be using a Paginated report that is referencing data in an Azure Analysis Services database and I will be referencing an Excel file that I have in OneDrive for Business which includes the needed information for the data driven subscription with 2000 records. The Paginated report is in a workspace backed by a Power BI Embedded A-SKU (A4 – equivalent of a P1 SKU) for testing purposes and the AAS tier is an S1 (100 QPU).

The report for this example is basic with just a single company name parameter defined that provides a list of the customers (first and last name) and the total amount due.

Paginated report example screenshot

With Power Automate you can use the pre-built Power BI templates that are available and then customize them as needed. The end result of the flow I created looks like the following –

Power Automate flow screenshot

The trigger for this flow is just using a schedule setup with a recurrence which is part of the template. For my demo I am just running it manually to show how it works and to highlight a few options you need to be aware of. For the Excel file it looks like the screenshot below and you can see a few records have already been processed based on the Processed and ProcessedDTM columns (these values are being updated in the flow) –

Excel file referenced for data driven subscription screenshot

In the file there is a RowId column so that I have a unique key value to reference for each record which gets used in the flow to update the processed information, CompanyName column which is used to set the parameter value in the report, user information for who will be getting the report, file name and format type, and then the processed information. The processed information is so I know when the record was last sent as well as if there is a failure or for some reason need to cancel the flow I can then restart the process and it would resume with just the records that are set to ‘No’ because of the filter included in the List rows setup for the Excel file –

List rows Filter Query expression screenshot

In this example instead of emailing files to users I am simply generating the PDF files and placing them in a OneDrive folder.

Pro Tip – It is always a good idea to test your flow out to make sure it will run smoothly without any errors, determine how long the process will take to complete, and verify this will not overload the premium capacity (especially if it is being used by end users for critical reporting).

So before emailing anyone I just want to make sure the process will run from start to finish without any errors. After the process does complete I will review the metrics and determine if any adjustments should be made.

Here are screenshots of the Export to File for Paginated Reports, Create file in OneDrive, and then the two Excel Update a Row steps.

Export to File for Paginated Reports screenshot

In the Export call I am specifying the workspace and report, you could actually provide the workspace id and report id in the Excel file to make this dynamic, referencing the File Format in the Excel file for the Export Format type, and then at the bottom setting the name of the parameter in the report (CustomerCompanyName) and referencing the CompanyName provided in the Excel file.

Create file in OneDrive screenshot

For creating the file I am dynamically setting the File Name based on columns in the Excel file (make sure to also include the file extension information, seems odd, but that is needed).

Excel Update a row screenshot (set processed info)

In the first Excel Update a Row step I set the Key Column which is RowId and make reference to the RowId value currently be processed in the loop and then set the Processed column to ‘Yes’ and ProcessedDTM column to current UTC time.

Excel Update a row screenshot (reset processed column)

In the second Excel Update a Row step once all of the records have been processed in the first Apply to Each loop I simply reset the Processed column back to ‘No’ for all of the records to reset it for the next time we need to run the flow.

After all of the steps have been configured and a test run is kicked off there are a couple of things that we will see, first it reads the Excel file records extremely fast, but after further investigation on the Apply To Each step you will see that it is only loading 256 records and in the Excel CompanyList table there are 2000 records;)

Initial flow run screenshot

So why is this process only loading 256 records? Hmm, thanks to Sanjay Raut who was working with me on testing this process out last year for report bursting he found this Solved: 256 rows limit – Power Automate and it turns out there is a Pagination setting on the Excel List Rows step that you need to turn on and then set accordingly depending on how many records you might have. In this case I simply set this to 2100 to cover my test sample.

Excel file list rows pagination setting screenshot

Now when we reset the Excel file and re-run the process we see that it took a little longer to read all of the records in the Excel file and that the Apply to Each step is going to loop through 2000 records:)

Update flow run screenshot

Another setting that you will want to be aware of is on the Apply to Each step which controls the concurrency. This is important and you will want to adjust this accordingly depending on how long it takes to run a report and stay within the Power BI connector throttling limits (100 calls per 60 seconds). If you do not enable this then the flow will only process one record at a time, in this test I have it set to 15 (20 is the default when enabled and 50 is the max).

Apply to Each step concurrency setting screenshot

I figure if I can process 30 to 60 reports per minute that is really good and will stay within the throttling limits. I don’t believe that will actually happen, probably more like 20 per minute, but we will see;)

For the second Apply to Each step to reset the file I don’t have the concurrency set to avoid any type of blocking, simply loop through the 2000 records and reset them back to Processed value of ‘No’.

Now that the flow is running the PDF files are being generated in the OneDrive folder as expected –

PDF files being loaded into OneDrive folder screenshot

I took a look at the progress after an hour and based on the Excel file I could tell that it had generated 1,384 files which is around 23 files per minute. Based on this information the process will complete in another 30 minutes, so 1 hour and 30 minutes from start to finish.

I reviewed one of the PDF files out in the OneDrive folder just to make sure everything looked good –

PDF file generated from the flow in OneDrive screenshot

Once the process completed I verified the file count and then reviewed the metrics from the Capacity Metrics App and AAS QPU utilization is Azure portal.

Verified file count in OneDrive folder screenshot

Capacity Metrics App screenshot during the flow process screenshot

With just this process running the capacity was not overloaded and used roughly 15% with the background process running which gets evaluated over a 24 hour period.

AAS QPU metrics from Azure portal screenshot

And AAS was barely being utilized maxed out at 5 QPU (the S1 has 100 QPU available).

Some things that I will need to compare this process against would be the following –

1. Moving the AAS database being referenced to a Power BI dataset hosted in the same capacity as the Paginated report
   • Post – Power BI Data Driven Subscriptions with Power Automate – Follow up using Dataset (2022-08-30)
2. Using a Power BI report instead of a Paginated report
   • Post – Power BI Data Driven Subscriptions with Power Automate – Follow up using Power BI report (2022-09-06)

I will do both of these comparisons as follow up posts, stay tuned.

Here are some references that you might find useful in the meantime –

• Export Power BI paginated reports with Power Automate
• Report BURSTING with the Power Platform (Guy in a Cube video by Patrick LeBlanc)
• Export paginated report to file

Just don’t forget when running this type of a load to test (and A-SKUs work great for this so you don’t impact production P-SKUs) and make sure to check out the additional settings pointed out earlier like the pagination and concurrency.

Power BI / AAS data model optimization v2

This is an updated version of the presentation I did earlier in the year which I recently presented for the Minnesota Data Saturday 13 event this past weekend.

Here is the direct link to the presentation on slideshare here.

Thanks to everyone that attended on Saturday and held out for the file sessions of the day:)

Kudos to the PASSMN board, sponsors, speakers, and volunteers for making this event possible!

Power BI / AAS Model Optimization Presentation

Updated (10/18/2021): There is now an updated version of this from a more recent presentation here.

Thanks to everyone that attended the MN BI User Group meeting today. Here is a link to my slides that I presented and if you have any questions about the content please feel free to reach out here, Twitter, or LinkedIn.

Here is the direct link Power BI / AAS Model Optimization presentation August 23, 2021.

Book Review: Microsoft Tabular Modeling Cookbook

There is a new Analysis Services (SSAS) Tabular book that is available to own and add to your library.  The book is written by Paul te Braak, who is a lead business intelligence consultant in Australia and is one of the developers on the DAX Studio project, and he has put together an outstanding cookbook.  When the book was released I was surprised and excited.  I was surprised because I did not know that Paul was working on this (he is the sole author, big kudos to Paul), and excited because I knew it was going to be a good one.  I had this one on my radar list of books to add to my collection; I am definitely a big fan of the Packt Publishing Cookbook series style of books. What I like about the books is that they introduce a topic or situation and then go over the solution in a very simple and easy to understand format – Getting Ready, How to do it, How it Works, There’s more.  Paul adds a lot of great insights in this book in explaining how the solutions work as well as including a bunch of ‘Tips’ along the way as well.

Here is when I first became aware that the book was released last month and posted the O’Reilly media deal:

So when I was asked by the publisher this past month to provide a review of the book I was more than happy to say ‘yes’.

Paul does a great job on slowly working you into the Tabular modeling concepts and the only tool you need to get going is Excel and the Power Pivot add-in.  Paul’s examples use Excel workbooks and flat files for the most part, so that makes it really easy to get started and get your learn on.

What is amazing is that this book is just over 300 pages and it is loaded with great content that covers items such as how to use Power Pivot, hierarchies, drilldown, parent-child hierarchies (including how to hidememberif in DAX), smart measures, smart keys, programmatic access in Excel – cube functions and VBA, querying with DAX, Power View, and more!  Simply amazing, Paul does a fabulous job and this is a great intro book that progresses into advanced topics and has great examples, tips, and insights that are a big time value add.

Here is a outline of the chapters:

Chapter 1: Getting Started with Excel
Chapter 2: Importing Data
Chapter 3: Advanced Browsing Features
Chapter 4: Time Calculations and Date Functions
Chapter 5: Applied Modeling
Chapter 6: Programmatic Access via Excel
Chapter 7: Enterprise Design and Features
Chapter 8: Enterprise Management
Chapter 9: Querying the Tabular Model with DAX
Chapter 10: Visualizing Data with Power View

Book link – Microsoft Tabular Modeling Cookbook.

I would definitely rate this as a must have for anyone doing tabular SSAS development and give it 5 out of 5 stars – . 

Awesome job Paul and thanks for writing the book and sharing!

Book Review: MDX with SSAS 2012 Cookbook

A couple of years back I reviewed a new book on the market related to MDX and Analysis Services, Book Review: MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook, and the book has now been updated to 2012.  The book was written by Microsoft SQL Server MVP Tomislav Piasevoli and Sherry Li. The cookbook style of book is an outstanding reference book.  The book is full of solutions that tell you “how to do it”, “how it works”, and wait, “there’s more” (great value add and insights).

The book has been updated for the 2012 edition of SSAS and the examples have also been updated against the 2012 version of Adventure Works database.  The other difference from the 2008 R2 release of the book is that some of content has been broken up into a a couple of new chapters.

• Chapter 1: Elementary MDX Techniques
• Chapter 2: Working with Sets
• Chapter 3: Working with Time
• Chapter 4: Concise Reporting
• Chapter 5: Navigation
• Chapter 6: Business Analytics
• Chapter 7: When MDX is Not Enough
• Chapter 8: Advanced MDX Topics
• Chapter 9: On the Edge

Book link – MDX with SSAS 2012 Cookbook. Amazon reviews link – Amazon Customer Reviews.

I would definitely rate this a must have for anyone doing multidimensional SSAS development and give it 5 out of 5 stars – .

Tabular Models, Compatibility Level, and Power View

[tweetmeme source=”denglishbi” only_single=”false”]

A couple of weeks ago I did a review of the CTP4 of SP1 that came out for SQL Server 2012 – Installing SQL Server 2012 SP1 CT4 Experience. Part of my test involved upgrading an Excel 2013 PowerPivot file to a Tabular Analysis Service project.  After I did that I setup a BI Semantic Model connection file in SharePoint and explored the Power View designer updates.  It was business as usual and I was seeing all of the new features and functionality that I had previously mention in my posts on Excel 2013 and Power View – Power View meet Microsoft Excel 2013 and Power View meet Microsoft Excel 2013 Part 2.

Well this past week I was prepping for my pre-con for the SQLSaturday #149 event and I noticed something odd.  When I was connecting to my tabular models with Power View I was not seeing my hierarchies or key performance indicators (KPIs).  I was a bit confused.  I upgraded my environment to SP1, so what was going on.  I thought the upgrade provided support for these model features now, right?  I know I had seen them already, maybe I was just imagining things though.  When I was doing my tabular project deployments I was also seeing a new dialog box that I was just clicking ‘Yes’ on without really understanding what it was talking about…

I completed out the pre-con still confused and then last weekend I did a bit more digging into this issue.  I went back to my Excel 2013 SQLSaturday model that I upgraded and I reviewed the property settings on the database.

The item that I noticed that seemed like it might be important was the Compatibility Level setting.  This database was showing SQL Server 2012 SP1 (1103).  I then took a look at the database I was using for some of my demos.

Ahhh, now the light bulb was starting to get bright:) The database that I was deploying was still in the SQL Server 2012 (1100) setting even though I had upgraded my environment to SP1.  Interesting.  Apparently you have the ability to control this setting now if you want to run in different levels or do some testing in the same environment before upgrading all of your databases.

I didn’t realize that I now had to make a change on my model property settings before I deployed my database.

Once this setting is changed you will be prompted with the following:

Once again I click ‘Yes’ and redeploy to upgrade the database on the server.  Now when I connect to my model with Power View I see my hierarchies and KPIs, perfect!

So just something to be aware of now when working with tabular model projects now.  There is a property setting that we have to take a look at, Compatibility Level, and it is a pretty important one for the users of our data models:)

Here are a couple of additional reference links to take a look at (some of this documentation and instructions might differ from what you see with CTP4 of SP1, but they should be available in the RTM of SP1):

• Compatibility Level (SSAS Tabular SP1)
• Configure Default Data Modeling and Deployment Properties (SSAS Tabular)

Cheers!

Book Review: MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook

[tweetmeme source=”denglishbi” only_single=”false”]

A couple of weeks back I mentioned a new book on the market related to MDX and Analysis Services and that I had received a copy to review.  The book is MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook written by Microsoft SQL Server MVP Tomislav Piasevoli.

This is by no means an introduction (101) book to MDX, but it is an MDX reference book that anyone using Analysis Services should have on their desk (or device – available in eBook version as well).  I have not read a cookbook technical style of book before and I simply couldn’t put it down.  I really enjoyed the style of here is a situation, here’s how you do it, and now let’s see how this actually works.  But the author doesn’t just stop there, nope, but what there’s more information about the recipe and then provides additional reference links to checkout or other spots in the book to look at as well.  After I got done reading one recipe I continued on to the next one because I was interested in reading the solution and finding out more and seeing how it compared to how I would approach the situation.

I found the book very easy to read and the information is extremely valuable for anyone working with Analysis Services and trying to solve solutions using MDX. This is a must have book for any SSAS professional, if you don’t believe me, check it out for yourself and you will definitely agree, just like others already have like Derek Goodridge (@WorkerThread) and Jason Thomas (@de_unparagoned).

Book link – MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook.

I would definitely rate this a must have and give it 5 out of 5 stars – .