sharing my experiences with the Microsoft data platform, SQL Server BI, Data Modeling, SSAS Design, Power Pivot, Power BI, SSRS Advanced Design, Power BI, Dashboards & Visualization since 2009
Last year Dun and Bradstreet, the world’s leading business data authority, recruited leading SQL Server MVPs to help their customers integrate business solutions using D&B services, and conducted a set of video interviews with 18 members of the MVP program community at the PASS Global Summit in Seattle. Details about the program are on the D&B MVP Program web site here. Many of these fine people are trusted friends, associates, and long-time members of the outstanding SQL Server community. Following is an excerpt of the announcement on the D&B MVP Program web site:
Each year the Professional Association of SQL Server (PASS) Experts holds their annual SQL PASS Conference. This year it was held Nov. 3rd and 4th for Pre-Conference Sessions and officially the conference ran from Nov. 5th through the 7th in Seattle, WA.
We hired a professional Hollywood Videographer to tape interviews of as many of our D&B MVPs that we could, so you could "get to know them better" both in their areas of expertise as well as in their role as a D&B MVP. We taped 18 of our current 42 D&B MVPs.
I truly hope you watch this video series, even one per night (each one is between 5-9 minutes long). It will show you the collective intelligence we have in our MVPs, it will show you their fun and personable personalities, and tell you how you can contact them online.
If you are looking for the industry top experts to help with data management, Business Intelligence, problem-solving or how to use any of the business data integration services from Dun & Bradstreet, these are the people to go to.
Here are the D&B MVPs (and two others) that were interviewed that day, in the order of the video series:
I just finished reviewing the November entries in the TechNet Wiki Guru competition and was quite impressed with the submission from Visakh Murukesan who submitted an article titled “Random SSRS Musings 2 : Simulating NOT IN and NOT LIKE Functionality In SSRS Filter Expressions”. I found this to be a well written post with excellent examples. These are both excellent examples of how simple expressions can be used to extend the capabilities of reporting features.
Can an SSRS report be designed to drill-through to an Excel workbook in-context (showing only the same filtered detail data)? I have to admit that I have been chasing this solution for a few years now before I was motivated enough to work it through to a viable result. More than a few consulting clients have asked if it is possible to build a dashboard solution with the option to drill-through to transactional details in Excel. The answer has always been “well, sort of” …we could only drill-through to an SSRS report and then export the contents of that report to Excel. That answer usually wasn’t good enough for the Excel power users who need to create their own workbook formulas and calculations, and use other Excel formatting and features; like PivotTables, slicers and conditional visualizations. Over the past few years, I have used some clumsy work-around techniques and discovered things like: if the target workbook were published in SharePoint and managed in a web part, workbook parameters can be used with great effort to achieve this task. However, that option has not proven to be a practical solution in most cases. As my good friend Steve Eaton once said: “Anything is possible if you have a positive mental attitude, tons of money and supernatural powers.” I’ll admit that I’m short on two of the three but I do have persistence and I’m bull-headed enough to apply a little out-of-the-box thinking now and again. The technique I will demonstrate will work in a standard Reporting Services deployment with any edition of Excel on the desktop.
Solution Demo
I’ll start with a quick demonstration of the finished solution. The Order Details report, shown here in Internet Explorer, gets data from a data warehouse (AdventureWorksDW2014 in this example). The order details and line items are stored in our line-of-business transactional database (for this demo, AdventureWorks2014). As you can see, I choose a data range using the standard date parameter UI. I’ve also exposed a Product parameter using a set of cascading lists and custom actions in the report header (I’ll cover that technique in another post). The relevant point is that we’re selecting some parameters to get a filtered view of the report data. After choosing the date range, I use the Category, Subcategory and Product lists in the report header to select Clothing, then Socks and then the product: Racing Socks, L. The cascading list simplifies the selection from among several hundred products.
A list of orders is displayed for the selected data range and product. In the list, I click the Sales Order Number for which I want to see the order and line details in my Excel report.
This displays a link “button” in the table header with a summary of the order number I selected.
When I click this link, the web browser confirms that I want to open the Excel file. This existing file stored in a network file share contains a connection to the transactional database with the order detail information. When the report opens, it applies the filters and shows the order with line item details. Our business users are thrilled with this because they’re actually using Excel with any features and capabilities they want. Rather than dumping a copy of data into a static table, live data is presented in PivotTables or charts which contain their calculation formulas and custom formatted data. If the business user decides to add another column, calculation, chart or other item to their report; they simply save it with those changes and use that as their detail report going forward. The new drill-through data just magically shows up in their workbook file with those additions the next time they drill-through from the summary report.
How Does It Work?
Reporting Services allows us to use parameters to pass information between reports – and that’s awesome if you’re only using Reporting Services. It allows you to maintain the context of properties and filtering options. But, if you’re not using Reporting Services and don’t have some kind of mechanism to “pass” parameters (like QueryString parameters to send information to a web page), we need to put those values some place so the target “report” (i.e. Excel workbook in this case) can retrieve them and apply them as filters. So, where would be a reliable place to store parameter information? How about SQL Server! Novel, huh?
To get started, open SQL Server Management Studio and create a database named ReportStandards. Let’s add all of the objects at once and then I’ll step through the use of each one. For demonstration purposes, I have not taken time to optimize this database and adding a few indexes would be advisable in a production scenario. Execute this T-SQL script:
Stores sets of parameter values for a user running a report
InsertReportUserFilter stored procedure
Inserts a parameter record into the ReportUserFilters table including the current user, table name, key value and a time stamp
ClearReportUserFilters stored procedure
Removes stored parameter records for the current user
GetReportUserFilters function
Returns a set of filter parameters to be used in a SQL WHERE clause IN function
PrepareReportSalesOrder stored procedure
An implementation for a specific report
I’ll open the finished SSRS report in Report Builder to show you the working solution.
There are several features of this report that aren’t directly related to the Excel drill-through action so I’ll fast-forward through those after a brief summary. This is like that scene in The Princess Bride where the masked swordsmen wakes up after being mostly dead and brought back to life by Miracle Max. Inigo says “let me e’splain… No. There is too much… let me sum up.” Here’s the summary:
The relevant report elements are circled in red and annotated with red numbers. Everything else is standard stuff that I would have designed into a report that doesn’t do this drill-through thing to Excel. The non-circled elements are parameters, datasets and other report elements that let the user interact with the report and filter a list of orders for a selected product and data range. I’ll refer to these numbers as I describe these report design elements.
Item 1 is a report parameter named SalesOrderNumber. A report action on the SalesOrderNumber textbox in the table (item 5) sets this parameter value. The parameter is defined as Text type, is hidden and has a default value of -1. The default value is a placeholder value that isn’t a valid SalesOrderNumber value.
The Orders dataset is just a plain old query that returns order records from the AdventureWordksDW2014 database filtered on the ProductKey, OrderDateFrom and OrderDateTo parameters. Nothing fancy here:
SELECT
rs.SalesOrderNumber
,rs.OrderDate
,rs.SalesAmount
,rs.ExtendedAmount
,rs.OrderQuantity
FROM
FactResellerSales rs
WHERE
rs.OrderDate BETWEEN @OrderDateFrom AND @OrderDateTo
and
ProductKey = @ProductKey
ORDER BY
rs.OrderDate
The table (Item 5) is unsophisticated as well. The SalesOrderNumber textbox in the detail row has a report action defined. Open the textbox properties window and select the Action page which looks like this in the designer:
The target report expression (labelled “Specify a report”) just refers to the Globals!ReportName object. This target re-runs this report when the user clicks this textbox. All the parameters but the last one are simply used to maintain their current values when the report is re-rendered. Ignore the ShowParameters item as well. The SalesOrderNumber parameter is set to pass the SalesOrderNumber field value so we know which order the user selected.
Item 3 is a dataset named SetupSalesOrderReport which serves two purposes. Most importantly, it writes the selected SalesOrderNumber value to a table so it can be used to filter the result set in the Excel workbook. This dataset is a simple stored procedure that returns a flag value used to display the drill-through link. Item 4 is a textbox that serves this purpose and its Hidden property is set to the following expression:
This simply says “if the SetupSalesOrderReport Result field value is ‘Success’, show this textbox”.
The SetupSalesOrderReport dataset is references the PrepareReportSalesOrder stored procedure and passes the SalesOrderNumber report parameter. When the selected order number is passed to the parameter in this report action, the procedure stores the value and returns “Success”. This, in turn, displays the textbox showing the link. The Value of the textbox uses the following expression to display a dynamic instruction to the user:
=”Sales order ” & Parameters!SalesOrderNumber.Value & ” details in Excel”
..and the Action for this textbox uses a Go to URL link using this expression:
=”file:\\\\tsclient\D\Projects\Excel Drillthrough Reports\Sales Order Details.xlsx”
Any valid UNC path will work here, prefixed with “file:\\”. This particular path is for a folder on my laptop that I am accessing from within a virtual machine I use for development and demonstrations. You will need to grant file system permission to the folder or share to be able to open this file.
The Excel “report” is a standard workbook. I’m using Excel 2013 but any supported version or edition of Excel will work. The important element of this solution component is the connection used to drive the PivotTables in the workbook. You can use a SQL statement to define a connection/query in Excel but it’s much easier to use a view. This report uses the following view which I created in the AdventureWorks2014 database:
create view vReportSalesOrderDetail
as
select
soh.AccountNumber,
soh.OrderDate,
soh.DueDate,
soh.Freight,
soh.SalesOrderNumber,
soh.PurchaseOrderNumber,
soh.ShipDate,
soh.Status,
cp.FirstName CustomerFirstName,
cp.LastName CustomerLastName,
sod.OrderQty,
sod.LineTotal,
sod.UnitPrice,
p.Name ProductName
from
Sales.SalesOrderHeader soh
inner join Sales.SalesOrderDetail sod on soh.SalesOrderID = sod.SalesOrderID
inner join Sales.Customer c on soh.CustomerID = c.CustomerID
inner join Person.Person cp on c.PersonID = cp.BusinessEntityID
inner join Sales.SalesPerson sp on soh.SalesPersonID = sp.BusinessEntityID
inner join HumanResources.Employee e on soh.SalesPersonID = e.BusinessEntityID
inner join Production.Product p on sod.ProductID = p.ProductID
where
soh.SalesOrderNumber in (select * from ReportStandards.dbo.GetReportUserFilters(‘FactResellerSales’) )
;
After selecting this view when defining the connection in Excel, the command text is simply:
I’ve updated the connection properties to refresh the data when the workbook file is opened. This will run the query and return live sales order data and apply the filtering logic that was added from the Reporting Services report.
Two PivotTables are added to the worksheet along with a calculated field (just to demonstrate that calculations can be performed in Excel rather than externally). Several styling enhancements are added in Excel such as data bars, font and color changes, hiding the grids, etc.
Enhancements
There is a lot of opportunity to enhance this solution depending on specific business needs. For example, a report name column can be added to the ReportUserFilters table to store user/parameter values separately for each report. I haven’t used the InsertDateTime column in this example but this could be used to go back and run the detail report for a point-in-time.
The previous example only inserted one parameter value but the following enhancement could be used to pass multiple selected parameter values from SSRS so they would all be included in the detail report:
Please join my associates and I for an all-day SQL Server Upgrade workshop on November 3rd
If you are planning an upgrade to a newer version of SQL Server, you won’t want to miss this all-day, preconference workshop.
Join John Martin, Sr. Technology Evangelist from Microsoft, will spend the morning discussing migration planning from SQL Server 2005.
In the afternoon, SolidQ mentors Ron Talmage, Richard Waymire, Jim Miller and Paul Turley will talk about and demonstrate upgrading specific workloads (projects and features) from older product versions to newer product versions. We will introduce the comprehensive SQL Server 2014 Upgrade Whitepaper that we recently wrote for publication by Microsoft.
Additional to upgrading specifically from SQL Server 2005 to SQL Server 2014, we will also talk about upgrading from SQL Server 2008 and 2008 R2 to SQL Server 2012 and 2014.
An upgrade and/or migration is far more than just a case of moving a database or installing a new version of SQL Server, there have been so many changes since SQL 2005 arrived that we need to do a lot of tasks to ensure we have a successful upgrade project.
This session will guide you through the process, looking at not only the technology but the methodology, processes and tools that we have at our disposal to make sure that when we do move from SQL Server 2005 to 2012/2014 or to the cloud with SQL Server in an Azure VM or Azure SQL Database that we do so in a way that we can be confident of success. We will take a special look at workload-specific upgrade needs for OLTP, HA, SSAS/SSRS, and SSIS environments.
In my afternoon section, I will demonstrate the capabilities of SSAS Tabular models and discuss upgrading and migrating Reporting Services from 2005, 2008 and 2008R2 to 2012 and 2014.
Thank you to the Microsoft MVP Program for awarding me MVP for six years running! I love this program and look forward to another year of great community events and opportunities to advise and mentor.
This year I had the privilege to speak at numerous SQL Saturdays, conferences and user groups, help develop a certification program for the University of Washington, contribute to books and papers, moderate forums, test new products and advise Microsoft product teams, direct the Oregon SQL PASS Chapter, and work with consulting clients to develop database and BI solutions using cutting-edge technologies. I’m planning the Oregon SQL Saturday this year and will be co-presenting a pre-conference at the PASS Global Summit.
I’m always looking for new opportunities to support the SQL Server and Microsoft Business Intelligence community. If you’re looking for a speaker, writer or need some help, please contact me.
I just received some bad news and found out that a friend passed away today. Reed Jacobson was one of the early leaders in the Microsoft Business Intelligence community. Somewhere around 2004-2008 I worked with Reed at Hitachi Consulting in Seattle where we travelled, taught classes and worked on client projects together. He was one of the earliest adopters and gurus in the BI community working for a small start-up called OLAP Train who helped bring SSAS (aka “OLAP Services”) to the masses. He spoke at many conferences and taught a lot of classes.
Not only was he one of the smartest people I’ve known when it came to solving business problems with Analysis Service & MDX but he was a kind, friendly man who really enjoyed helping and connecting with people. He wrote many books about data analysis with Excel & SSAS and he wrote the MDX training curriculum for Hitachi Consulting. He was my mentor. We co-taught some of those early classes and I spent some time with him on the road. He bent my brain intellectually and we talked about work, family, politics and religion. He was man of principles who asked questions and gave a lot of thought to his actions.
Reed left a lot more than a collection of old technical books behind. He had many good friends, customers and connections who appreciate his leadership, guidance and friendship. I am privileged to have worked with Reed.
A memorial will be held this Sunday, July 20 at 7:00 PM
Dr. Jose Rizal Park; 1008 12th Ave S, Seattle, Washington 98134
I’ve created a simple query performance logging tool for Analysis Services, called the SSAS Performance Logger. The tool allows you to navigate through the metadata for a tabular model and select from measures and table attributes to build a simple query which is executed and timed. The query can be executed many times to get an accurate sampling of test variations and to test performance while various loads are placed on the server.
I literally created this over the weekend and plan to add additional capabilities as time allows – so please check back. To provide feedback, add comments to this post.
My initial objective was to choose two different measures that were alternate methods of calculating the same value, run them side-by-side and then see which performed best. Then it occurred to me that we can run any number of measure performance tests in iterative loops and compare the results by playing back the captured log results. Since the results are captured in a log database, the test results for each measure or query can easily be compared and analyzed using tools like Excel and Reporting Services.
One of my main objectives for a future version of the tool is to add logging for server performance counters like memory usage, thread counts and CPU capacity; while these queries are running.
What you will need:
I’ve developed SSAS Performance Logger for use with SSAS Tabular in SQL Server 2012. To manage the logging database, it uses the OLEDB data provider for SQL Server 2012 which may be backward compatible as far back as SQL Server 2008. It also uses the ADOOMD data provider version for Analysis Services. When MDX support is added to a future version, it should support SSAS sources as far back as SSAS 2008.
System requirements:
Microsoft .NET Framework 4.5
ADOMD 6.0 (can be installed with the SQL Server 2012 SP1 Feature Pack)
An instance of SQL Server 2008 or better with administrative/create database rights for the logging database
With these components installed, you should be able to copy the executable to a folder and run it.
To install:
Download the zip file and extract it to a folder on your system
Verify that the required dependent components are installed
Run SSAS Perf Tester.exe
The first time it runs, the application will check for the logging database and prompt you to create it
On the SSAS Query page, enter the Analysis Services server or instance name and press Enter
Select a model or perspective, measure and table/attribute from the drop-down list boxes to build the query
Click the Start button to run the query once and see how long it took to run
On the Options page, type or select the number of times to repeat the query
Click the Start button the run the query in a loop. The results are displayed in a line chart showing the duration in milliseconds for each execution
Every query execution is logged for later analysis. Here is a view of the logItem table in the SSASPerformanceTest database:
This fact is probably not documented very well but this is an important factor in SSRS visual report design…
The use of rectangles is very much a core part of Reporting Services report design and a difference in behavior is expected. Cells in a tablix will grow vertically to accommodate wrapping text and when that happens any object on the same row will stretch to fit the row height. For images, charts (which are rendered as images), gauges, indicators (which are gauges) and sparklines (which are charts); this means that the image must be resized and scaled to fit. Image scaling adds a lot of rendering overhead. If you place any of these objects in a rectangle, this prevents the image scaling and speeds things up considerably. This is by design and a recommended practice.
In several comments to that post, users noted slow rendering when charts and sparklines were used in a table. In addition to the rectangle trick, one user suggested “Setting the SizeToReportContent to false on the report viewer allows the images to be consolidated into one image and the performance improves dramatically.”
In July, 2012, Jason Thomas posted on a similar topic:
In that post, he provides step-by-step instructions with images, to use rectangles in a dashboard-style report to correct the rendering issue. He also has several posts on related topics.
A Getting-Started and Survival Guide for planning, designing and building Tabular Semantic Models with Microsoft SQL Server 2012 Analysis Services.
by Paul Turley
This post will be unique in that it will be a living document that will be updated and expanded over time. I will also post-as-I-go on the site about other things but this particular post will live for a while. I have a lot of good intentions – I know that about myself and I also know that the best way to get something done is to get it started – especially if I’m too busy with work and projects. If it’s important, the “completing” part can happen later. In the case of this post, I’ll take care of building it as I go, topic by topic. Heck, maybe it will never be “finished” but then are we ever really done with IT business solutions? I have been intending to get started on this topic for quite some time but in my very busy project schedule lately, didn’t have a concise message for a post – but I do have a lot to say about creating and using tabular models.
I’ve added some place-holder topic headers for some things that are on my mind. This list is inspired by a lot of the questions my consulting customers, students, IT staff members and business users have asked me on a regular basis. This will motivate me to come back and finish them and for you to come back and read them. I hope that you will post comments about your burning questions, issues and ideas for related topics to cover in this living post about tabular model design practices and recommendations.
Why Tabular?
SQL Server Analysis Services is a solid and mature platform that now serves as the foundation for two different implementations. Multidimensional models are especially suited for large volumes of dimensionally-structured data that have additive measure values that sum-up along related dimensional attributes & hierarchies.
By design, tabular architecture is more flexible than multidimensional in a number of scenarios. Tabular also works well with dimensional data structures but also works well in cases where the structure of the data doesn’t resemble a traditional star or snowflake of fact and dimension tables. When I started using PowerPivot and tabular SSAS projects, I insisted on transforming data into star schemas like I’ve always done before building a cube. In many cases, I still do because it’s easier to design a predictable model that performs well and is easy for users to navigate. A dimensional model has order and disciple however, the data is not always shaped this way and it can take a lot of effort to force it into that structure.
Tabular is fast for not only additive, hierarchal structured data but in many cases, it works well with normalized and flattened data as long as all the data fits into memory and the model is designed to support simple relationships and calculations that take advantage of the function engine and VertiPaq compression and query engine. It’s actually pretty easy to make tabular do silly, inefficient things but it’s also not very hard to make it work really well, either.
James Serra has done a nice job of summarizing the differences between the two choices and highlighted the strengths and comparative weaknesses of each in his April 4 blog post titled SQL Server 2012: Multidimensional vs Tabular. James points out that tabular models can be faster and easier to design and deploy, and that they concisely perform well without giving them a lot of extra attention for tuning and optimization. Honestly, there isn’t that much to maintain and a lot of the tricks we use to make cubes perform better (like measure group partitioning, aggregation design, strategic aggregation storage, usage-base optimization, proactive caching and cache-warming queries) are simply unnecessary. Most of these options don’t really exist in the tabular world. We do have partitions in tabular models but they’re really just for ease of design.
What About Multidimensional – Will Tabular Replace It?
The fact is the multidimensional databases (which most casual SSAS users refer to as “cubes”) will be supported for years to come. The base architecture for SSAS OLAP/UDM/Multidimensional is about 13 years old since Microsoft originally acquired a product code base from Panorama and then went on to enhance and then rewrite the engine over the years as it has matured. In the view of many industry professionals, this is still the more complete and feature-rich product.
Both multi and tabular have some strengths and weaknesses today and one is not clearly superior to the other. In many cases, tabular performs better and models are more simple to design and use but the platform is lacking equivalent commands and advanced capabilities. In the near future, the tabular product may inherit all of the features of its predecessor and the choice may become more clear; or, perhaps a hybrid product will emerge.
Isn’t a Tabular Model Just Another Name for a Cube?
No. …um, Yes. …well, sort of. Here’s the thing: The term “cube” has become a defacto term used by many to describe the general concept of a semantic model. Technically, the term “cube” defines a multidimensional structure that stores data in hierarchies of multi-level attributes and pre-calculated aggregate measure values at the intersect points between all those dimensions and at strategic points between many of the level members in-between. It’s a cool concept and an an even cooler technology but most people who aren’t close to this product don’t understand all that. Users just know that it works somehow but they’re often confused by some of the fine points… like the difference between hierarchies and levels. One has an All member and one doesn’t but they both have all the other members. It makes sense when you understand the architecture but it’s just weird behavior for those who don’t.
Since the tabular semantic model is actually Analysis Services with a single definition of object metadata, certain client tools will continue to treat the model as a cube, even though it technically isn’t. A tabular Analysis Services database contains some tables that serve the same purpose as measure groups in multidimensional semantic models. The rest of the tables are exposed as dimensions in the same way that cube dimensions exists in multidimensional. If a table in a tabular model includes both measures and attribute fields, in certain client tools like Excel, it will show up twice in the model; once as a measure group table and once as a dimension table.
(more to come)
Tabular Model Design: The Good, the Bad, the Ugly & the Beautiful
I’ve taught a few PowerPivot training sessions to groups of business users (now, remember that Tabular SSAS is really just the scaled-up version of PowerPivot.) Admittedly I’m more accustomed to working with IT professionals and when I teach or work with users, I have to throttle my tendency to go deep and talk about technical concepts. In these classes, I find myself restating the same things I’ve heard in conference presentations and marketing demos about PowerPivot data sources, like “you can import just about anything into PowerPivot”. As I read the bullet points and articulate the points on the presentation slides to these users, I have this nagging voice in the back of my mind. I’ve spent many years of my career unraveling the monstrosities that users have created in Access, Excel & Visual Basic.
Whether stated or implied, there is a common belief that a PowerPivot solution doesn’t require the same level of effort to transform, prepare and cleanse data before it gets imported into a data model. For many years, we’ve been telling these users that it will take a serious effort, at significant cost, to prepare and transform data before we can put it into a data mart or cube for their consumption. In a typical BI solution, we usually burn 70-80% of our resource hours and budget on the ETL portion of the project. Now, using the same data sources, users are being told that they can do the same thing themselves using PowerPivot!
Data Modeling 101 for Tabular Models
One of the things that I really enjoy about building tabular models is that I can have my data in multiple structures and it still works. If the data is in a traditional BI “Kimball-style” Star schema, it works really well. If the data is normalized as it would be in a typical transactional-style database, it still works. Even if I have tables that are of a hybrid design; with some characteristics of both normalized and dimensional models, it all works beautifully.
Here’s the catch; one of the reasons we build dimensional data model is because they are simple and predictable. It’s really easy to get lost in a complex data structure and when you start combining data form multiple source systems, that’s where you’re likely to end up. Getting business data into a structure that is intuitive, that behaves correctly and gives reliable results can be a lot of work so be cautious. Just because a tabular model can work with different data structures doesn’t that you don’t need to prepare your data, clean it up and organize it before building the semantic model.
The classic star schema is one of the most effective ways to organize data for analysis. Rather than organizing all data elements into separate tables according to the rules of normal form, we consolidate all the measures that are related to common dimensional attributes and with a common grain (or aggregation level), into a fact table. The dimensional attributes are stored in separate dimension tables – one table per unique business entity, along with related attributes. Any group of measures not related to the same set of dimensions at the same level would be stored in their own fact table. In the example, Invoice measures that are related to stores and customers, recorded every quarter are in one fact table. The sales debit records for customers and stores that are recorded daily go in a different fact table. The account adjustments don’t record the store key but they are uniquely related to accounting ledger entries stored in the ledger table. Note the direction of the arrows showing that facts are related to lookup values in the dimension tables.
Exhibit 1 – A Fully conformed Star Schema
If you can pound your data into the shape or a star schema and this meets your requirements; this is what I usually recommend. It’s a simple and predictable method to organize data in a well-defined structure. Now, let’s look a variation of this approach that has characteristics of both the star schema and normalized form. We’ll call this a “hybrid” model.
The following hybrid schema contains two fact tables in a master/detail relationship. The cardinality of the Invoice and LineItem tables is one-to-many where one invoice can have multiple line items. This would be considered a normalized relationship with the InvoiceID primary key related to the an InvoiceID foreign key in the LineItem table.
The Invoice table contains a numeric measure called Invoice Amount that can be aggregated by different dimensional attributes. Those attributes, such as Store Name, Customer Name or any of the calendar date units in the Dates table that are organized into a natural hierarchy (with levels Year, Month and Date). To facilitate this, the invoice table is related to three different dimension tables: Stores, Customers and Dates. Each of the dimension tables has a primary key related to corresponding foreign keys in the fact table. The LineItem table also numeric measures and is related to the Products table, also a dimension table.
Exhibit 2 – A Hybrid Star / Master-Detail Schema
This semantic model supports two levels of aggregation with respect to the Invoice and LineItem records. If I were to browse this model in an Excel Pivot Table and put all the stores on rows, I could aggregate the Invoice Amount and see the sum of all Invoice Amount values for each store
<< need pivot table graphic here >>
Are There Rules for Tabular Model Design?
Oh, absolutely. Tabular SSAS and PowerPivot allow you to work with data is a variety of formats – structured & unstructured, dimensional & normalized. You have a lot of flexibility but there are rules that govern the behavior and characteristics of data. If you don’t follow the rules, your data may not meet your requirements in the most cost-effective way.
This reminds me of an experience when I started high school.
Rule #1: Model the data source
Rule #2: Cleanse data at the source
Rule #3:
Tabular Model Design Checklist
What’s the Difference Between Calculated Columns & Measures?
What are the Naming Conventions for Tabular Model Objects?
What’s the Difference Between PowerPivot and Tabular Models?
How to Promote a Business-created PowerPivot Model to an IT-managed SSAS Tabular Model
Getting Started with DAX Calculations
DAX: Essential Concepts
DAX: Some of the Most Useful Functions
DAX: Some of the Most Interesting Functions
Using DAX to Solve real-World Business Scenarios
Do I Write MDX or DAX Queries to Report on Tabular Data?
Can I Use Reporting Services with Tabular & PowerPivot Models?
Do We Need to Have SharePoint to Use Tabular Models?
What Do You Teach Non-technical Business Users About PowerPivot and Tabular Models?
What’s the Best IT Tool for Reporting on Tabular Models?
What’s the Best Business User Tool for Browsing & Analyzing Business Data with Tabular Models?
Survival Tips for Using the Tabular Model Design Environment
How Do You Design a Tabular Model for a Large Volume of Data?
How Do You Secure a Tabular Model?
How to Deploy and Manage a Tabular Model SSAS Database
Tabular Model Common Errors and Remedies
Tabular Model, Workspace and Database Recovery Techniques
Scripting Tabular Model Measures
Simplifying and Automating Tabular Model Design Tasks
Tuning and Optimizing a Tabular Model
How do you tune a tabular model? You don’t.
You can prevent performance and usability problems through proper design.
When I started playing with GeoFlow, I just had to find a way to do something interesting with it. This is a short world tour with visits to a few SQL Server community members and leaders around the globe talking about their SQL Server communities. GeoFlow is a new BI tool currently in development at Microsoft. It’s an add-in for Excel 2013 using the SQL Server Analysis Services tabular in-memory aggregation that plots and visualizes data points on the globe using imagery from Bing Maps. After visualizing data on a map, you can create a 3D tour with full zoom and navigation in 3D geographical space.
Thanks to my friends and associates who have contributed to this effort thus far. This is the first draft so please check back and I’ll add more stops on the tour along with more sophisticated data points. The dataset in my current GeoFlow workbook is very light and with more contributions, I’ll can add creative ways to visualize the global SQL Server world community. Enjoy!
GeoFlow is still in development and not yet generally available. It’s too soon to foresee when it will be complete enough for a public preview and then for release but I’ll let you know what I know when I know it.
Today in Vancouver, British Columbia, at the SQL Saturday #198; I presented a session titled “Data Visualization Choices”. As promised, my slide deck is available for download here.
This is the first draft of the session I’m preparing for the PASS Business Analytics Conference coming up on April 11-12 in Chicago. I’ll have another update for that conference.
The email message earlier today said “Please note that the official schedule will be released tomorrow, February 6th, so please do not share your schedule until that time.” Well, what does that mean, exactly? It means that it’s midnight right now and time to tell the world…. We are going public, people! The PASS Business Analytics 2013 session schedule has been completed, the speakers are working on their presentations, feverously practicing their demo material and rehearsing in the mirror, to no one in the car, to the kids and to the dog who already knew you were crazy before all this mad ness began. Yep, we take this stuff pretty seriously. Continue reading →
When “SQL Azure” first became available a couple of years ago, I played with it, created a few databases and then quickly left it behind but in the last few months I’ve found some really good uses for “Microsoft Windows Azure SQL Database”. Continue reading →
Just wanted to get the word out that our SSRS 2012 training course is available. Are you looking for expert training on SQL Server 2012 Reporting Services? SolidQ now offers a SSRS training course with focus on designing report solutions for Business Intelligence and SharePoint integration. Not using SharePoint? Private classes can be adapted to use Reporting Services in native mode. Training is available for public and private events, onsite or remote delivery.
Designing BI Report Solutions with SQL Server 2012 Reporting Services and SharePoint 2010 Continue reading →
Creating a report with two datasets that reference the same pair of parameters, I’m seeing the following error:
The Value expression for the query parameter ‘DatesYear’ contains an error: The expression references the parameter ‘DatesYear’, which does not exist in the Parameters collection. Letters in the names of parameters must use the correct case. (rsRuntimeErrorInExpression)
—————————-
An error has occurred during report processing. (rsProcessingAborted) Continue reading →
