Search results matching tags 'Analysis Services', 'MDX', and 'BIDS'

Vote a bug and a missing feature in #powerpivot #denali #ctp3 on #connect

sqlbi — Tue, 19 Jul 2011 07:24:00 GMT

If you have 30 seconds available, please vote the following items on Connect:

https://connect.microsoft.com/SQLServer/feedback/details/679618/import-attribute-key-from-ssas-in-powerpivot

When you import data coming from an Analysis Services cube in PowerPivot, you cannot import the attribute key value. Only the attribute name is included in the query generated by the query designer. There is a possible workaround, creating a calculated measure with an MDX expression which return the attribute key value corresponding to the current member of the related attribute, but not many users are able to do that.

https://connect.microsoft.com/SQLServer/feedback/details/679677/ragged-hierarchies-cannot-be-imported-in-powerpivot-tabular

When you import data coming from an Analysis Services cube in PowerPivot, you cannot import a ragged hierarchy. I've already described this issue in an older post.

The reason I ask your support is that in Denali CTP3 both PowerPivot and BIDS for Tabular projects has these issues. It really hurts PowerPivot end users, but it is also a waste of time for the experienced BI developer, who need to write a calculated measure in MDX for each attribute key he wants to import it using the query designer (writing MDX by hand is always the preferred alternative, I know) and have to write an MDX query by hand if the levels in the ragged hierarchy are not exposed as browsable attributes (which is not a best practice in cube design!).

Help me to rais the priority for this issues - A multidimensional model in Analysis Services should be a better data source for PowerPivot and BISM Tabular!

The Microsoft BI Roadmap: BISM, UDM and Beyond

sqlbi — Mon, 15 Nov 2010 13:05:00 GMT

Microsoft recently announced a new roadmap for its BI architecture. The next version of SQL Server, codenamed “Denali”, is going to introduce a new semantic model named BISM (Business Intelligence Semantic Model). Analysis Services will host it and it will be queryable through MDX and DAX. DAX has been introduced in PowerPivot as an expression language, but it will be extended in Denali to provide also query capabilities, but it will keep its nature of a “functional” language.

A more complete description about this roadmap has been published in a blog post made by the SSAS development team. Since we still don’t have a working beta product to test (CTP1 of Denali doesn’t include any new feature in SSAS) I can only make some consideration based on the many information I gathered at PASS Summit 2010 and during private meetings and conversations with members of the SSAS development team. You can of course read other interesting posts from Chris Webb and Teo Lachev to look at some concerns the announcements made at PASS have been raised up in the MS BI Community.

In the long term, the Microsoft strategy is to provide a platform for BI to everyone that will provide the same basic building blocks to any user interested in building a data model for any kind of reporting or analytical needs. Many tried to do the same in the past, and Microsoft tried the same too by introducing UDM (Unified Dimensional Model) several years ago. UDM is great to build models that can be expressed in a multidimensional way, but it might be too complex to be used for simple reporting purposes. Its learning curve requires a certain investment just to start with a simple project. And many developers that are used to SQL simply refuse to approach MDX and UDM just to build a few reports. For these reasons, and also to contrast other vendor’s products, Microsoft is going to introduce a new “big thing”, which is BISM.

To describe BISM, the best thing is looking at PowerPivot today. You can define a model by simply defining tables, relationships and calculations, which are made by using DAX. These concepts are very familiar to both Excel users and developers who are used to relational databases. So, why not using SQL? The reason is that in PowerPivot (and then in BISM) the relationships are part of the model, whereas in a RDBMS a relationship is just a relational constraint. And, most important, DAX is a language that is very simple at the beginning, and that can be learned in a very incremental way. Under the cover, there is a calculation engine called Vertipaq. It is very fast. Faster than any competitor and also faster than columnar indexes that will be implemented in SQL Denali. But BISM will also allow querying an underlying relational database in pass-through mode – in Denali only SQL Server will be supported for this type of real-time usage. Something that is very important to enable BISM as the “unified model” for any reporting need. Finally, to query BISM you can use MDX and, in Denali, also DAX (which will be extended for this purpose), making it easier to express a query over a set of unrelated tables, something that would be nearly impossible in MDX and UDM today.

BISM sounds very promising and the long term strategy is very consistent. What caused many concerns in many of us is the transition strategy. After many discussions and many thoughts, I have this roadmap to share with you:

UDM is here to stay. It is a full multidimensional model that can be used to create complex models with complex calculations. If your business model fits well in a multidimensional model, this is something that can make your life easier
BISM will not replace UDM. At least, it will not replace all the feature of UDM very soon. In the long term, BISM will be able to satisfy all of the requirements of any data analysis and reporting needs. But in its first release it will not have this level of coverage.
BISM will be far better of similar products of other vendors, even if UDM will be more advanced of BISM for very specific requirements. At least, this is the goal for Microsoft. If you look at BISM and UDM in this perspective, it gives much more sense to the overall architecture. BISM will be much more interesting than UDM to customers that are used to other BI technologies, which are less advanced than UDM but good enough for their own requirements.
Existing UDM implementations will continue to work in SSAS. There are no reasons to plan a migration by now. Only after BISM will be released in a version that will be able to satisfy all the existing requirements for your project, than a migration might be considered. But it will not be required, because UDM is not going to be deprecated. The recent case-study of a 12TB cube implemented by Yahoo! should be a good point to support this statement.
New projects starting before the Denali release should be implemented by using UDM. Only in case where UDM doesn’t fit the requirements (i.e. massive leaf-level calculations resulting in low performance) then an early adoption of Denali should be considered.
New projects starting after Denali release should be implemented in BISM if it fits all the requirements. Probably, many projects that wouldn’t have implemented in UDM today (because some SSRS reports on a RDBMS are “good enough”,) might be considered for a BISM implementation. This is probably the key selling point for Microsoft: getting new customers for Analysis Services by offering BISM as a more affordable entry point for a BI solution than UDM. Ideally, this category will contain also all those projects that today would be implemented in UDM just because it is the only “semantic model” that they have today to make a user able to navigate data by using Excel.
In the years to come, as long as the BISM will be always more feature-complete compared to UDM, it will become a viable alternative to UDM. Only time and user adoption will tell if BISM will be able to completely replace UDM. From my point of view, it will require at least three release cycles to reach a point of real competition. It means that we will see new projects starting in UDM at least since 2015. Considering the traditional policy support of Microsoft, any investment made on UDM will be safe at least until 2025/2030. It’s a very long time.

Thus, I’m really confident with the strategy about the server side. I still need to hear more news about the client-side, even if rumors seem better than actual evidence.

Excel is the primary BI client tool. It navigates data by using MDX. It natively supports both UDM and BISM. It seems that there is an important ongoing effort that will see the light in the next release of Excel. I really don’t have any other information here and I can only speculate about some of the former ProClarity features will be implemented inside Excel. What I know is that the resources that are involved in the BI client part of Excel are higher than ever today.
Crescent is the codename for a new ad hoc reporting and data visualization tool that functionally resembles Data Analyzer. Yes, it is completely new, much more graphical, more interactive… but the basic idea is fundamentally the same. It is (like Data Analyzer was) a complementary tool to Excel, and not an alternative one. This tool was supposed to generate queries only in DAX. This would exclude the possibility of querying an existing UDM model. However, I would wait a few weeks for an official statement by Microsoft about Crescent support of existing UDM models.
Reporting Services and Report Builder should support BISM in a native way. Today it already supports UDM through MDX. It should be able to query BISM in MDX as well, but supporting DAX should be considered to make life easier to those developers who are not used to MDX. I don’t have information about this kind of support, but it should be the natural evolution.
I haven’t heard any news about PerformancePoint, but I can imagine it will have BISM support as a natural evolution as well. However, because PerformancePoint should be aligned with Excel, we should see a new version of Excel and PerformancePoint only in 2013, I suppose. However, MDX will be available to query BISM from PerformancePoint, in case a Service Pack with BISM support will not be released in time.

As you can see, we are just at the beginning of a major wave of innovation in the BI space. In this case, the innovation start from the Self-service BI and will grow-up until it will reach the corporate BI at a more pervasive level. A key point of the Microsoft strategy is the Vertipaq engine. Only in these days I started to understand how much disruptive this technology can be. I know very well that several UDM cubes in these days run on server that have more RAM than the cube size. Not every project is inside these boundaries, but many are. And with Vertipaq compression, the bar is simply higher.

Finally, these are my advices for the current and future BI developments:

If you are a company who want to start a BI project, don’t wait and go to UDM now.
If you are a BI firm or consultant, start your training for DAX by using PowerPivot. It is an excellent tool for prototyping and you can use it to train yourself and to prepare proof of concepts of BI models for your customers. Then continue the implementation using UDM by now. Commercial: my recent book has several chapters about DAX, too.
When a feature-complete CTP of Denali will be available later next year (maybe not very soon) start to explore it to understand its capabilities and whether they can fit your requirements or not.
Once BISM will reach a feature set that satisfy your requirements for a new project, start to consider it because development time might be considerably lower and skills required could be easier to build, especially if your data model is not too complex.
Whatever you do in your professional life, if you are reading this blog you have to learn DAX. You can start today and my recent book can be a good start point also to cover more advanced data models and calculations.

A final thought is about MDX. I know that mastering MDX is hard, but I cannot say that DAX is so simpler. Yes, it is simpler at the beginning, but for more complex calculations, the required DAX expression might be more complex than the corresponding MDX one. Coming from a relational background (SQL) DAX is more intuitive at the beginning, but coming from an MDX background it is easier to learn the more advanced part of DAX that allows you to create the more complex and powerful expressions that solve real-world complex problems in a very efficient way. Thus, also your investments in MDX are preserved. Your MDX queries will still run and you will still be able to write new MDX queries. But the more important asset you have is the MDX knowledge and understanding, which puts you in pole position to really master DAX too, even if a further study will still be required.

DateTool dimension: an alternative Time Intelligence implementation

sqlbi — Sun, 02 Sep 2007 13:41:00 GMT

Year-To-Date and Difference-Over-Previos-Year (or Year-Over-Year Growth) are among the most required features of any user. Some OLAP client (like ProClarity) offers features that try to solve this problem client-side, but I don’t like this approach given that you might have a server-side feature doing that (so you don’t discriminate Excel users).

One trivial approach is creating a calculated measure for each combination of measure and date-calculation. While this works, it results in a multiplication of the number of measures, making the cube hard to use. Analysis Services offers a feature called Time Intelligence Wizard that creates some calculated members on dedicated Date attributes. This works in MDX but has several limitations:

· It limits the measures on which the calculation are applied (each time you add a new measure, you need to update the wizard-generated MDX scripts)

· It only applies to selected hierarchies – it doesn’t work, for example, if you cross Month and Years on rows and columns.

· It doesn’t work well with Excel 2007 after you install Analysis Services 2005 SP2 – see my rants here.

While the first two issues can be solved by using the same architectural approach of Time Intelligence Wizard, simply writing a different MDX Scripts, solving the third issue (Excel 2007 compatibility) requires a different architecture. For the sake of Google/MSN Live desperate users, I’m going to describe “how to make calculated members working on non-measures dimension with Analysis Services 2005 SP2 and Excel 2007”. I hope that Microsoft will consider a similar approach on a future version of Time Intelligence Wizard…

You can download the solution I'm going to describe on SQLBI.EU web site, under DateTool Project.

The issue

Let’s start describing the Excel 2007 SP2 issue. I created a small subset of Adventure Works sample cube. Calendar Date Calculations is the wizard-generated attribute. With BIDS browser (built using OWC 11) you can put this dimension on the columns of a pivot table with Calendar hierarchy placed on rows, and you can select any set of members from the Calendar Date Calculations attribute. In this example I unchecked one of the members generated by Time Intelligence Wizard.

With Excel 2007 the same cube is usable only if you enable the “Show calculated members from OLAP server” on the PivotTable Options dialog box (it’s unchecked by default).

Now we can generate a PivotTable similar to the one generated with BIDS browser.

Unfortunately, we cannot select single members from the Calendar Date Calculations attribute. We only have an “all or nothing” option based on the PivotTable Options settings we’ve seen above. This is the resulting Excel 2007 PivotTable.

The solution

I defined a dedicated dimension for time-related calculated members. Each formula has its own “real” members, instead of calculated ones. This solves the Excel 2007 issue at the price to require a dimension process instead of a simple MDX deployment (changing MDX Scripts for new calculated members wouldn’t require cube reprocessing). Since I don’t want a cube space growth, I simply put the dimension in the cube without a relationship with any measure group.

This is the real tricky and most non-intuitive part of the game. The DateTool dimension is seen as a regular dimension by any client, but it can be changed and reprocessed without any need to reprocess any measure group (because it has no relationship!).

The DateTool dimension could contain a single attribute with all time-related calculated members. However, I prefer an approach that uses two sets of different calculated members, which are orthogonal and might be crossed together. For example, I would put Year-To-Date calculation in one attribute and Year-Over-Year Growth in another one, so that I can obtain the Year-Over-Year Growth of a Year-To-Date calculation without the need to create a dedicated calculated member. In other words, I use these two sets to separate aggregations from comparisons formulas.

In practice, I would need two independent dimensions, and I do that from a relational point of view. I define two views in a separate schema on my data source (I could also use a separate Data Source View with self-contained named queries, but I prefer using relational views as a general way to decouple relational schema from multidimensional one – reasons for this would fill a whole dedicated article).

CREATE SCHEMA DateTool
GO

CREATE VIEW [DateTool].[DateAggregation] AS
SELECT 0 AS ID_Aggregation, 'Regular' AS Aggregation
UNION ALL
SELECT 1 AS ID_Aggregation, 'Year To Date' AS Aggregation
UNION ALL
SELECT 2 AS ID_Aggregation, 'Last 12 Months' AS Aggregation
UNION ALL
SELECT 3 AS ID_Aggregation, 'Total Current Year' AS Aggregation
GO

CREATE VIEW [DateTool].[DateComparison] AS
SELECT 0 AS ID_Comparison, 'Regular' AS Comparison
UNION ALL
SELECT 1 AS ID_Comparison, 'Previous Year' AS Comparison
UNION ALL
SELECT 2 AS ID_Comparison, 'Diff. Over Previous Year' AS Comparison
UNION ALL
SELECT 3 AS ID_Comparison, 'Diff. % Over Previous Year' AS Comparison
GO

Since I want to build a single dimension with two attributes, I create another view to build a junk dimension with the desired cardinality.

CREATE VIEW [DateTool].[DateTool] AS
SELECT 
    a.ID_Aggregation, 
    s.ID_Comparison, 
    CAST( ID_Comparison AS VARCHAR ) + ' - ' + CAST( ID_Aggregation AS VARCHAR ) AS Description
FROM DateTool.DateAggregation a
CROSS JOIN DateTool.DateComparison s
GO

At this point I can import these views in the Data Source View. I specify Primary Keys and Relationships manually because they can’t be inferred by relational metadata (they don’t exist at that level).

The resulting DateTool dimension is very simple.

At this point there is the necessary MDX Script that solves all DateTool members. This single part would require another post or two and I don’t have time to explain that now, but you can see the complete AdventureWorks based solution downloading it from SQLBI.EU.

The interesting part is the result provided by Excel 2007 with this solution. Here is an example of the Calendar hierarchy crossed with a selection of the calculated members available in the Aggregation attribute of the DataTool dimension.

With this dimension, there are no limitations like those involved by calculated members (see the previous example made using Time Intelligence Wizard). In the previous PivotTable there is a member unchecked, like you can see in the following picture.

Just using this technique you could translate each calculated measure generated by Time Intelligence Wizard into a “regular” dimension fully usable by Excel 2007 users. The presence of two independent attributes makes the user able to build more complex PivotTables like the following one: the highlighted column displays the Year Over Year Growth over the Year To Date value.

Undoubtedly, such a feature is comfortable more to advanced users that to inexperienced one, since often a rename of the resulting report is required to better describe query results.

Finally, one of the limitations of Time Intelligence Wizard is that generated formulas work on a limited part of the cube: only one time hierarchy and only a selected set of measures. With the MDX Scripts I used, there are no similar constraints, making the cube easier to navigate. In the following example, I put years on columns and months on rows, displaying the Year-To-Date value and the difference with the previous year for selected data.

Unfortunately, with this approach we lose compatibility with other clients, like the Browser provided by BIDS and SQL Server Management Studio. A PivotTable like the previous one would not display any data: the different way Excel 2007 and OWC 11 manage dimensions metadata produces this discrepancy between results obtained by difference OLAP clients. I am not sure if this can be solved just changing MDX Scripts definition – I made many tests until now and I described the best compromise I obtained until now, but I’d like to get feedback about issues, possible improvements and eventually alternative architectural solutions.

If you have time to take a look at the solution, please send me your feedback and/or any other comments at marco.russo (at) sqlbi.eu or using the comments for this post.