I’m sure you’re all aware of the difference between natural and unnatural user hierarchies in the Analysis Services Multidimensional model (if you’re confused as to what I mean when I say ‘Multidimensional model’, have a quick read of this post from a few weeks ago which explains the terminology). To recap, natural user hierarchies in Multidimensional look like this in BIDS:

There is a one-to-many set of attribute relationships between each level, so each Calendar Year has multiple Calendar Semesters but one Calendar Semester has only one Calendar Year, and they are a Good Thing as far as query performance is concerned. Unnatural user hierarchies look like this:

They work, but there aren’t one-to-many relationships between every level and query performance may be worse than with a natural user hierarchy.

But what does all this have to do with the Tabular model? In SSDT when you create a hierarchy there’s no indication as to whether it’s natural or unnatural:

…and up to recently I assumed that this was an issue that simply wasn’t relevant to Tabular. However, after a recent conversation with Marius Dumitru from the dev team and Greg Galloway I now know this isn’t the case!

If you query the MDSCHEMA_HIERARCHIES DMV as follows:

SELECT
[DIMENSION_UNIQUE_NAME], [HIERARCHY_NAME], [STRUCTURE_TYPE]
FROM $SYSTEM.MDSCHEMA_HIERARCHIES

You can see whether a hierarchy in a Tabular model is natural or unnatural:

In this case you can see that the Calendar hierarchy that I created on the DimDate table is unnatural; SSAS has determined this during processing (specifically during the Process ReCalc stage) by examining the data itself automatically.

It turns out that natural hierarchies in Tabular can result in faster query performance because certain MDX and Formula Engine code paths in the SSAS engine are still not optimised for unnatural hierarchies. I don’t have any specific examples of when this occurs at the moment but if I do find them I’ll be sure to update this post. And if anyone else using Tabular, or even PowerPivot (and I assume this is relevant to PowerPivot too) finds a good example of how changing to a natural user hierarchy improves performance please leave a comment.

In the example above, I created the Calendar hierarchy in the Tabular model by simply dragging the CalendarYear, CalendarSemester, CalendarQuarter, EnglishMonthName and FullDateAlternateKey columns underneath each other in the new hierarchy. It’s unnatural because there are only two distinct values in Calendar Semester (the semester numbers 1 and 2), four distinct values in Calendar Quarter (the quarter numbers 1 to 4) and there are only twelve distinct values in EnglishMonthName (the names of the months), so there is a many-to-many relationship between the values in all these columns. I can make it natural by creating three calculated columns that concatenate CalendarYear and CalendarSemester, CalendarYear and CalendarQuarter, and CalendarYear and EnglishMonthName as follows:

DimDate[Calendar Semester of Year] =DimDate[CalendarYear] ” ” DimDate[CalendarSemester]

DimDate[Calendar Quarter of Year] = DimDate[CalendarYear] ” Q” DimDate[CalendarQuarter]

DimDate[Calendar Month of Year] = DimDate[CalendarYear] ” ” DimDate[EnglishMonthName]

Using these calculated columns for the Semester, Quarter and Month levels of the hierarchy as follows:

…will make the Calendar hierarchy natural:

Advertisement

Article source: http://feedproxy.google.com/~r/wordpress/Cpjz/~3/mlTp5KvouCc/

Just to echo what Marco said in his blog post from last week, the PASS BI Virtual Chapter is currently looking for presenters, especially from Europe or European-friendly time zones. If you are interested please send some ideas for abstracts plus some information about yourself and your speaking experience to PASSDWBIVC@sqlpass.org. Presenting online is a great way to get speaking experience, especially if you are not able to attend conferences or user groups on a regular basis. We hope to hear from you soon!

Advertisement

Article source: http://feedproxy.google.com/~r/wordpress/Cpjz/~3/Di2T99gpPXk/

Almost every time I write MDX calculations, I end up using scoped assignments – it’s by far the easiest way to control where your calculations work inside a cube. However making sure that your scoped assignments work in the places that they’re meant to work and don’t overwrite each other can be very tricky indeed (for a brief introduction to the subject and these problems, see this session I gave at SQLBits a year or so ago), so in this post I’m going to show you how you can use the MDX Script Debugger in BIDS to help you do this.

Despite its name the MDX Script Debugger doesn’t actually help you to debug individual MDX expressions or calculations. What it does is clear the MDX Script of the cube then allow you to step through each statement in the MDX Script, applying them one after the other, so you can see the cumulative effect on a query. This is only really useful when you’re working with scoped assignments because it allows you to see which cells in your query are changed with each successive assignment.

To illustrate, let’s use the Adventure Works cube. Comment out everything on the MDX Script (ie what’s on the Calculations tab) except the Calculate statement and add the following code to the bottom:

CREATE MEMBER CURRENTCUBE.MEASURES.DEMO AS 1;

SCOPE(MEASURES.DEMO);
    SCOPE([Date].[Date].MEMBERS, [Date].[Calendar Quarter].[Calendar Quarter].MEMBERS);
        THIS=2;
    END SCOPE;
    SCOPE([Date].[Calendar Year].[Calendar Year].MEMBERS);
        THIS=3;
    END SCOPE;
END SCOPE;

SCOPE([Measures].[Internet Sales Amount]);
    SCOPE([Date].[Date].MEMBERS, [Date].[Calendar Quarter].[Calendar Quarter].MEMBERS);
        THIS=2;
    END SCOPE;
    SCOPE([Date].[Calendar Year].[Calendar Year].MEMBERS);
        THIS=3;
    END SCOPE;
END SCOPE;

Then deploy your database and go to Debug menu in BIDS and click Start Debugging:

The MDX Debugger screen will then be displayed:

In the top pane you can see the Calculate statement highlighted – this is the point in the script that has been reached as you step through the code. In the bottom pane you have a browser control where you can construct a query plus four panes where you can enter your own hand-written MDX queries.

In the browser control, drag the Internet Sales Amount measure onto columns and the Calendar hierarchy from the Date dimension onto rows, until you see something like this:

You don’t see any data at the moment because the Calculate statement hasn’t been executed yet (see here for more details on what the Calculate statement does). If you hit F10 to step to the next statement in the MDX Script you’ll see the values for Internet Sales Amount appear because the Calculate statement has now been executed:

If you hit F10 again, the calculated member DEMO will be created and you can now drag it into the browser; at this point you’ll see it always returns the value 1 because none of the scoped assignments have been executed yet:

Hit F10 again until you reach the first END SCOPE statement and you’ll see the following:

You can see that MEASURES.DEMO now returns 2 for the Date, Month and Quarter level as a result of this first assignment; you can also see that only the values that have been affected by this assignment have been changed. Hit F10 some more to execute the second assignment and you’ll see that DEMO returns 3 at the Year level and the affected cells are again highlighted:

Notice how, in this case, because you’re scoping on a calculated measure only the cells you scoped on have their values changed. This is in contrast with scoped assignments on regular measures: because regular measures aggregate up, scoping on a regular measure not only affects the values in the cells you scoped on, but those values will then also be aggregated up though the cube.

To show what does happen when you scope on a regular measure, look at the next set of scoped assignments on the Internet Sales Amount measure. The first assignment scopes on the Date, Month and Quarter levels and sets their values to 2; however the Year level values now show the aggregated totals of all the quarters in the year, so if there are four quarters in a year then the year will show 4 * 2 = 8. The All level total is also similarly affected.

The final assignment sets the Year totals to 3 for the Year level; this overwrites the values that have been previously aggregated up from the Quarter level, and the Year level values are again aggregated up to the All level:

Hit F10 some more and you’ll reach the end of the MDX Script, whereupon you’ll go back to the beginning and can start all this again. Go to the Debug menu and click Stop Debugging to finish. Useful bit of functionality, isn’t it? Certainly one of the least-known features of BIDS too.

One last point – if you try to use the Debugger and hit the infamous SSAS Locale Identifier bug, check out Boyan Penev’s post here on how to solve this issue.

Advertisement

Like this:

Article source: http://feedproxy.google.com/~r/wordpress/Cpjz/~3/ZCd9s-orOZA/

Over six years ago now I wrote what proved to be a very popular post here on my blog called “So, what is the UDM?”. It was written in response to the widespread confusion around the time of the release of Analysis Services 2005 about what the term “UDM” actually meant. In a sense “UDM” was just another name for an Analysis Services cube, but it also represented the nebulous concept of Analysis Services being a semantic layer suitable for all your reporting needs; however people often thought it was a new product that somehow replaced Analysis Services cubes and got themselves tied in all sorts of knots as a result. Thankfully, use of the term died out pretty quickly as everyone started referring to Analysis Services as, well, Analysis Services.

Fast forward to the present day and I can see a similar amount of confusion about the term “BI Semantic Model” or BISM for many of the same reasons. What is the BI Semantic Model exactly? It is… Analysis Services 2012 plus PowerPivot. Let’s be clear though: it is not just the new Analysis Services Tabular Model, although the term BISM is all too often used as if it did mean that. It’s not even a specific bit of software. Analysis Services 2012 consists of two parts, the Multidimensional Model which is the Analysis Services of cubes and dimensions that we already had in 2008 R2 and earlier versions, and the new Tabular model which is the Analysis Services of tables, relationships, in-memory storage and column store. BISM refers to both models plus PowerPivot, or rather it refers to the way that Analysis Services and PowerPivot can be used as a semantic layer on top of other data for reporting purposes.

So what’s the point of a term like BISM then if it doesn’t refer to something tangible? Why not just call Analysis Services “Analysis Services” and PowerPivot “PowerPivot”? Well there’s certainly some conceptual stuff going on here (as outlined in the Vision and Roadmap blog post) but just as we had with the term UDM I’d say there’s also some marketing-led obfuscation here as well, for three reasons:

• A single term like BISM suggests that Analysis Services 2012 and PowerPivot are a single, cohesive product, whereas the Tabular and Multidimensional models are actually very different beasts. If you’re going to be working with Analysis Services 2012 on a project the first decision you’ll have to make is which type of model to use, and if you change your mind later you’ll have to start development again from scratch and learn a lot of new skills. I hope one day that the two models will merge again but it won’t happen soon.
• Microsoft has correctly identified that many people want to do BI but were put off by the complexity of building Multidimensional models in previous versions of Analysis Services. The simplicity of the Tabular model goes a long way to solving this problem; Tabular also replaces Report Builder models which were really a just a simple semantic layer for people who didn’t like SSAS or had valid reasons to stay with relational reporting. In order not to scare off this market a new name is necessary to avoid the negative connotations that come with “Analysis Services” and “cubes”.
• Calling something a “semantic model” suggests that it’s a nice, light, thin, easy-to implement layer on top of your relational data warehouse, with no data duplication (which is often seen as a Bad Thing) involved. In actual fact anyone who has used the Multidimensional model will know you almost always use MOLAP storage which involves all the data being copied in Analysis Services; and I suspect when people start using the Tabular model they will be using it in Vertipaq mode (where again all the data gets copied into Analysis Services) rather than in DirectQuery mode (where all queries are translated to SQL which is then run against SQL Server).

Now I’m not going to suggest that there’s anything wrong with these marketing objectives – anything that sells more Analysis Services is good for me – or that the Tabular model is bad, or anything like that. All I want to do is suggest that in the SQL Server technical community we stop using the term BISM and instead refer to Analysis Services Multidimensional, Analysis Services Tabular and PowerPivot so we’re always clear about what we’re talking about in blog posts, articles, books and so on. I don’t think using the term BISM is useful in any technical context, just as the term UDM wasn’t in the past, because it is a blanket term for several different things. I also think that so many people are confused about what the term BISM actually means that it is becoming counter-productive to use it: people will not buy into the MS BI stack if they’re not really sure what it consists of.

What does everyone think about this?

Advertisement

Article source: http://feedproxy.google.com/~r/wordpress/Cpjz/~3/XZMaXYw6ZOc/

It is absolutely true fact that vehicle buying can appear to be an extremely exciting period of your life, as you get to drive a great deal of dealerships and try out a diversity of available automobiles until you find the perfect solution that feels right and is nice to drive.

Still it is unlikely that buying a vehicle of your dreams is such an easy thing to do. Therefore, if you need to find an automobile that ideally fits your individual situation and budget and whether you like the actual model or not, it is not a smart idea to purchase the first vehicle you find. Indeed, there is always the solution to get the vehicle on a finance agreement, but even in this event you may have some problems with paying it off.

Fortunately these days if you have no financial opportunity to pay for your vehicle monthly when loaning it, you may always opt for automobile leasing solution which is known to be much cheaper.

It is true that this solution generally appealed to businesses because it permits them to supply automobiles to their employees without paying the full cot normally required for them. Actually, the way that leasing a car works is easy to understand and in its simplest form is just an event of a business or person leasing an automobile off a leasing organization, paying them a particular monthly fee and handing the automobile once the vehicle leasing contract has finished. Some contracts (such as PCP) give people a great possibility to buy out the automobile hired at the very end of the leasing contract if they desire. It’s surely the perfect solution for getting some relevant company fleet for several years, as it means that the company doesn’t need to pay out for the entire value of the car, but just for its usage.

It goes without any sayings that leasing a car solution has become incredibly popular with non-company vehicle buyers in several of recent years as this solution offers them exactly the same privileges as in the case individuals were a business, thus offering them a possibility to lease an automobile for a predetermined period of time. The actual reason that it can be very advantageous for people who can’t afford purchasing automobiles by other means is that as they don’t actually own the vehicle leased. Still if you opt for this solution you don’t have to pay for its full cost, meaning that if you choose to buy some vehicle, you are sure to pay a half of the rate you are likely to be charged in the event you get a loan for your vehicle. So, I really see no reason why avoid hiring some affordable leasing a car deal.

It is true that a great majority of people is searching for some relevant solution for finding the best automobile lease deal with the affordable monthly payment. Actually this is nothing but a pretty difficult, time consuming and laborious process. In general it is worth mentioning that there are several ways for people to start searching for the best automobile lease deal possible!
1) Negotiate A Vehicle Leasing Contract With Your Local Dealer
It is a pretty great idea to go to your favorite vehicle dealer and opt for the negotiation of a leasing a car deal with a sales starting with the buying price of the automobile then the monthly fee. Before you do that, it is a pretty smart idea to opt for some reliable websites to get to know more about new vehicle pricing and MSRP.

2) Get A Vehicle Lease Quotation From The Dealer Through Email
It is very luring to take the advantage of the WWW by emailing the online fleet manager of the best dealerships regarding leasing a car offers. All you need to do is to tell them the model and make of the automobile you desire to lease, wait for the quotes offered and begin your comparison of all the leasing solutions just available for you particular situation. Remember that you may get from the dealers a great diversity of solutions so choose attentively. It is a smart idea to pick the dealership that provides you with the most competitive solutions and start the negotiating the conditions of the lease deal. In general, the price estimate from the WWW fleet manager is going to be more competitive which should offer you much better place to begin the negotiation.

3) Find Out Automobile Lease Offering Specials In Papers
Believe my experience that it is a pretty great idea to read the ads in the newspapers for available leasing solutions for the day or the week. But it is worth mentioning that the great majority of the solution of the type are only valid for a rather short period of time and are of very limited supply.

4) Get Leasing Quote From Different Websites
It is a pretty wise thing to use advertisements and websites that claim to provide you with the best leasing a car programs. They ask applicants for some personal info so they can offer further info of the leasing deals.

5) Compare Automobile Lease Deals
It is absolutely true that using some websites is a great opportunity as this allows auto lease shoppers to have a little bit of comparison for available leasing points in terms of monthly fees, down payment, and leasing length for the best vehicles from diversity of dealers. This will greatly save time for the vehicle shoppers.

It is true that in the majority of cases automobile leasing depends a great deal of the depreciation costs of the automobile. This cost is normally quite dependent on long term automobile leases for more than a few years. So there is a great diversity of very important factors which generally determine the depreciation coat of a leasing a car. Keep in your mind that such issues as the model and make of the vehicle as well as the year of make as just several items that are strongly recommended to be considered. Actually in the case the model of automobile is pretty expensive one, then the depreciation cost is generally much slower in comparison to ordinary cheaper ones and this makes the automobile leasing costs considerably cheaper. If for example expensive automobile hired through the leasing a car solution depreciate over 15 % over a leasing period and cheaper automobile can depreciate 50%, in comparison to the costs of both vehicles it is not any complicated to calculate the percentage of the vehicle depreciation. In this event you can easily make out that it is really cheaper to lease some more valuable automobile than the cheaper vehicle.

It is worth bearing in the mind that automobiles always depreciate in their value the more miles they come. This is an incredibly essential and vital factor to pay your attention to when considering some vehicle leasing solution. So if you want to buy a car after a stated leasing period, then you are to consider the factors which greatly influence the depreciation cost of the auto and do an attentive check before choosing anything to buy.

It is absolutely true fact that great deals of people happen to like the vehicle model which they have taken on leasing a car contract and after a particular period of time you intend to purchase it. The automobile leasing company normally takes into account the fees you are charged some of the months previously and deducts it from the general price of the vehicle. But, it is really essential that you check if the depreciation cost has also been taken into account according to normal percentage.

So it is essential that when considering various solutions you can opt for when leasing an automobile at the key factors mentioned in this article, certainly it seems that automobile leasing a valuable car which has a lower depreciation cost might be a little bit cheaper solution than buying a brand new vehicle. On the other hand, it also greatly reduces the maintenance fees and wear and tear payments. That is the reason why if you are still having no vehicle, I strongly recommend you to choose some costly one to lease.

It is absolutely true fact that the great diversity of automobile leasing plans are getting more popular and widely used as they will help you save a good deal of money on vehicle ownership. In this short article we will look at some particular reasons why you are strongly recommended to consider a vehicle leasing plan when you purchase your next automobile.

What Is Automobile Leasing?
An auto leasing plan is a great solution of renting a new automobile over an agreed period of time. Actually are not considered to be a legal owner of the automobile leased at any point and at the end of the leasing a car you simply hand the vehicle back to the company offering the leasing services.

No Great Cash Payments Needed
A leasing a car plan is not a type of automobile loan so a big first deposit is not generally required. You are simply charged a small upfront fee, which is often about two months of the car rentals. This means that you will have a possibility to keep your capital in a best interest earning account and pay the stated costs per month as part of your constant living expenses.

This is considered to be pretty beneficial for businesses that are able to pay the lease rates out of operational more willingly than capital fees. The good news is that businesses have a possibility to reclaim the VAT fees with the leasing a car costs, whereas VAT on purchasing a new automobile cannot get reclaimed. This way a business owner is likely to save a great deal of their money.

The Automobile Is Covered By Relevant Warranty
As most automobile manufacturers offer a warranty lasing for three years, your that is the reason why you may be perfectly sure that your leased vehicle will generally be covered by the original manufacturers warranty, as long as the contract is for three years.

It goes without any objection that this way you will have a possibility to save a lot of money, hassle and worry as you need just pay for the simple routine services and some items like tyres, and there won’t be great deal of those required during the three years you hire your vehicle for.

Change Your Vehicle When You Want To
It is true that leasing an automobile is a great solution in the event you if you enjoy driving around in a new automobile every several years then vehicle lease solution is a great option for you. As you are not considered to be an owner of the vehicle, you needn’t have any worries about attempts to sell it before purchasing another automobile. You will simply hand the vehicle back to the automobile leasing company and choose another car.

It is absolutely true fact that a vehicle leasing broker acts as a middleman between the Finance Company and the person or company intending to lease an automobile. He is responsible to checking a number of funding providers on an everyday basis (which is quite similar to an insurance broker) to determine which is the most relevant on a particular auto.

It is also true that because of the large diversity of business the car lease broker deals with the main auto dealers, he is able to negotiate the best discount on the desired model of vehicle just possible. This great automobile discount, together with the low rate charged by finance company, means that the dealer you are opting for is able to has an opportunity to usually offer a much lower vehicle leasing rentals than is available right from a main vehicle dealer.

So, in this article I will introduce you the Top ten things to search for when making your choice of a vehicle Leasing Broker.
1. It is true that the independence is the most vital issues to consider. It is necessary to keep in your mind that car lease broker should not be closely linked and dependable to any one automobile manufacturers or finance companies

2. You are to be offered a broad portfolio of finance company to make sure that you will be offered the most competitive quote. A car broker is to be well placed to compare the whole of vehicle leasing market to find you the most advantageous deals

3. Well established car lease business will have with a proven track record. That is the reason why it is so essential to ask to see testimonials and referrals from satisfied and content customers
4. It is really essential that you try to keep you well informed as to the progress of your car lease order and offer one convenient point of agreement for all your issues or queries about your automobile or contract, so you will face no need to contact the finance company too often or supplying vehicle dealer personally.
5. The company you contact is to offer free of charge delivery to your house or office, and free collection in the end of termination of the agreement
6. It is also true that the pooled mileage arrangements are to be offered, as this might potentially make substantial reducing in the costs wasted for companies with numerous automobiles
7. Should provide you with a full range of automobile services and solutions including maintenance agreements, fleet compounding management, accident management, gap insurance coverage, duty of care compliance, short term lease and fuel cards to mention but a few. Remember that if you have some questions about your lease, contact a dealer

It goes without any sayings that modern people consider that a house without a vehicle is not a full house. In addition many people need to rent a vehicle fleet for their business. Indeed to have a relevant look of your business it is vital that your vehicles are really prosperous. But the problem is that buying an automobile and even several vehicles is not any cheap solution. That is the reason why most people are searching for some other possibility to get the vehicles needed. This is just the stage when vehicle leasing solution comes into the stage.

It is absolutely true fact that the solutions of vehicle rentals and car lease service providers offer you several choices to all tastes and requirements. If you do a little bit of research you are sure to have a great deal of facilities from where you have a possibility to pick up the best automobile or several automobiles to fit your style and pocket ideally. It is also worth mentioning that high capacity automobiles that are most generally hired by travelers and tourists, customers are also offered some business automobiles such as Ikon, Benz, Indica, Accord, Fiesta to mention but a few. The automobile leasing services are not limited only up to leasing and renting nowadays. It is true that some of them also promise you to offer hotel booking services along with corporate car lease services and car rent services. Some of the companies of the type are specialized in offering cab services for airport delivery and drops.

Anyway it is worth mentioning that car lease services subject to great diversity of conditions that may potentially vary according to the models of vehicles and the terms and conditions of the services providers. It is true that some of the most frequently opted conditions that are applicable while selecting a relevant solution an automobile leasing service are like returning the car in relevant condition. It is also necessary to admit that the automobile you lease must not exceed the maximum driven mileage in order to avoid any additional charges. In fact, you are to be aware that there are some vehicle leasing companies that have even set a minimum criteria for age for the driver below which the driver will not be allowed to drive the hired automobile. Most essential to all the above issues, the current driver’s license is generally required while hiring an automobile.

It goes without any saying that purchasing a vehicle is most likely a great investment, and that is the actual reason why all people want to make sure that they are investing at the right type of automobile and in the relevant car lease company which suits perfectly.