Category Archives: SSAS

Bienvenidos al SQL Saturday #189 – Costa Rica

Posted on by jeffprom

SQL Saturday #189

I’m excited to announce that I will be speaking at SQL Saturday #189 in beautiful Costa Rica! This event will be held on April 13th, 2013 at the Aurola Hotel Holiday Inn San Jose Downtown.
I will be presenting on the following two topics:

Master Data Services
Does your company have similar data in multiple locations? Not really sure which set of data is accurate? With MDS, Microsoft gives you the ability to have one accurate source of non-transactional data to use with your applications or send to other databases and ETL processes. Leverage data stewards to help manage the data repository to ensure it is up-to-date and accurate. In this session we will cover MDS from start to finish including installation, creating models/entities, working with data, using the Excel add-in, security, hierarchies, views, versions, and business rules. After this session, you should have a good understanding of what MDS does, how to use it, and how to get started with your own MDS project.

Data Quality Services
As the old adage goes, ‘Garbage in, garbage out.’ Stop spending hours manually cleansing your data, and start using DQS! DQS is the new data quality tool provided with SQL Server 2012. By leveraging knowledge bases, you can correct invalid data and improve the quality of your data.

In this session we will cover creating a knowledge base, data cleansing, data matching, and using 3rd party knowledge bases (Reference Data). We will talk about installation, show how to use the DQS tool, and how to integrate DQS into your SSIS packages. After this session you should have a good understanding of what DQS is, how to use it, and how to get started with your own Data Quality initiative.
………………………………….
Last November I was fortunate enough to meet several chapter members at the PASS Summit event in Seattle. I look forward to the opportunity to meet again in San Jose this April.

Check out the SQL Saturday #189 for more information.

Advertisement

Minnesota PASS User Group – 1/15/2013

Posted on by jeffprom

pass
Next Tuesday I will be presenting on using Proactive Caching in SSAS at the Minnesota User Group. The meeting runs from 4-6pm at the Microsoft Technology Center. For more info you can check their website here: http://minnesota.sqlpass.org/ Hope to see you there. 🙂

Advanced Cube Design – Part 1: Proactive Caching
In this first session of a multi-part series on advanced cube design techniques we will explore using proactive caching. Proactive caching gives you the ability to roll data into your cube automatically as it becomes available while still maintaining MOLAP level performance. We will cover what proactive caching is, when to use it, and how to implement it.

Proactive Caching in SSAS

Posted on by jeffprom

OLAP cubes use a dimensional model based on Facts and Dimensions. Cubes provide pre-processed aggregations based on available data values. If your cube is small (in the MB’s) and not heavily used, you may want to simply process your cube several times a day. An easy way to do this is by creating an SSIS package and using the ‘Analysis Services Processing Task’.

Proactive caching allows data to continually stream data into the cube. As data comes in, SSAS notices the new data and automatically rolls it into the cube. If you have a lot of small cubes, proactive caching is great because you can keep these cubes up-to-date and you don’t have to schedule a lot of SSIS packages to refresh them all. Cubes are typically data bound by the underlying data sources and their ETL. If you have a very efficient ETL, or no ETL at all, proactive caching may be a good choice for you since data will flow right into the cube.

Ok, let’s take a look at how to enable proactive caching on your cubes. If we were to simply turn on proactive caching on the fact table(s) that link to a new dimension value, we could run into trouble. To avoid this we need to first turn on proactive caching on each dimension that could have new data coming in, or where data values may be updated. Go to each Dimension in the cube and go to the properties tab. Near the bottom, expand the Storage section. The default options are Molap and no proactive caching. Click the details button to pull up the Dimension Storage Settings window.

Click on Options.

Check the box on the top to ‘Enable proactive caching’. Feel free to look into the various settings and make adjustments how you like but I’m going to keep the defaults. The silence interval waits x amount of time since it last saw a data change before processing the SSAS dimension. In this case, it would wait 10 seconds after the last data change before it would go ahead and process the dimension. This is helpful if you are having a bunch of updates to the data source, SSAS won’t try to process the dimension every single time there is an update. The silence override interval means that if data has continually been changing for x amount of time (in this case 10 minutes), go ahead and process that dimension after 10 minutes and then continue to do the silence interval checks.

Next, click on the Notifications tab. On this tab we need to specify the source table or view for this dimension.

On this screen check the box that says ‘Specify tracking tables’ and click the button on the right. Select the source table or view for this dimension. This tells SSAS that when there is a data change on this source, update the cube dimension as well.

Note: As the message on the bottom indicates, in order for SSAS to notice data changes, the SSAS service account has to use an account with either System Administrator or ALTER TRACE privileges. For demo purposes, I set my SSAS service account to run as LocalSystem. Click ‘OK’ to exit this screen, and ‘OK’ on the Storage Options screen. Notice that the storage options on the properties tab now says ‘Automatic MOLAP’.

Continue to do this same step for each of your dimensions that could have data changes. Then do the same on your cube but be sure to select your Fact table data source (table or view). Now process and deploy your cube to SSAS and test it out. Connect to your cube from Excel and pull up some data. Try changing a value on your existing fact table in Management Studio. Wait 10 seconds, or whatever silence interval you specified, and refresh your excel data. You should now see the updated data, and we didn’t have to re-process anything! Next, try adding a new dimension record and a new fact record that uses the new dimension record. Again, after waiting 10 seconds and refreshing the data in Excel you should now have a new dimension record as well as a new fact record.

Proactive caching lets you continuously add new and updated data into your cube without having to schedule processing. Where proactive caching really comes in handy is when you have a lot of small cubes, or your cube is based on top of an OLTP system. If you have a lot of small cubes, with proactive caching you don’t have to a lot of cube refreshes with SSIS packages. Cubes are dependent on the underlying data and their ETL processes. If your cube sits directly on top of OLTP data, you are able to avoid the ETL bottleneck and proactive caching can help feed data automatically into your cube keeping it up-to-date.

Code Mastery (Minneapolis Oct 2nd, 2012)

Posted on by jeffprom

Code Mastery Logo

I will be speaking at this years Minneapolis Code Mastery event on October 2nd. I will be presenting on how to use SSRS Reports with SSAS Cubes. This event offers FREE, high-quality technical content on business intelligence and Window 8 development.

Code Mastery Minneapolis is hosted by Magenic with the goal to present attendees with meaningful technical content by the professionals that are using the subject matter every day.

The featured speaker for the Mineapolis event is Magenic CTO and CSLA .NET creator Rockford Lhotka. Along with a great agenda, we’ll make time for you to network and make some great connections. Come spend a day a the Microsoft Technology Center in Edina, MN with some of Magenic’s best and brightest.

Pass this on to your friends and colleagues that may be interested in the event. And feel free to send a Linkedin invite if you’d like to connect with me directly.

WEBSITE LINK: http://codemastery.com/minneapolis/

Hope to see you on October 2nd!

MDX vs T-SQL in SSAS

Posted on by jeffprom

Using SSAS in BIDS, you need to remember that you should be using MDX instead of T-SQL when designing Calculated Members. Here is an example of how both would be used to prevent a divide by zero error.

T-SQL
-- Comment Block
CASE WHEN [Measures].[Measure2] = 0 THEN 0
ELSE [Measures].[Measure1] / [Measures].[Measure2]
END

MDX
// Comment Block
IIF([Measures].[Measure2] = 0, 0, [Measures].[Measure1] / [Measures].[Measure2])

Both will work, but the T-SQL one will give you some warnings. It’s best to remember to use MDX instead.

SSAS Formatting a Currency Calculation

Posted on by jeffprom

In Analysis Services, when designing your cube in BIDS, under the Calculations tab you can create calculated values by using Calculated Members. If your calculation happens to be a currency type, you would be happy to find that under the Format string drop-down there is a “Currency” option. However, in my experience this doesn’t actually format the value as you would expect. To show your value with dollar signs, and comma’s you can put in this value instead:
(include the double quotes)
“$#,##0.00;($#,##0.00)”

Formatting SSAS currency values