ssas

Attribute in multiple hierarchies in Analysis Services 2008

Hi! I have designed a relatively simple data warehouse that uses the star schema. I have a fact table with just a primary key along with CompanyID and Amount (the actual measurement) columns. Of course I also have a dimension table to represent the companies which the fact table references. Now I'm required to create a single level hie...

SSAS - Inferred Dimension Attributes - are they necessary?

I have a cube with fact tables that have a parent-child relationship: Person - Case. Some dimensions are inferred from the parent, e.g. State in the example below. Person does not have a foreign key to the State dimension, but Case does, and through the Person-Case relationship the State is inferred. By doing it this way, I end up with ...

Reporting Services Aggregate not working for parent-child relationships

I have a parent-child attribute relationship in an Analysis services dimension, say [Dim].[Child] is a child of [Dim].[Parent] and I have a query, that returns the set UNION( [Dim].[Parent].Members * [Dim].[Child].Members, [Dim].[Parent].[ALL] * [Dim].[Child].Members ) on axis 1 and some output on axis 0. The query works fine whe...

SQL Server Analysis Services 2008 and ByAccount Aggregations

Hi I am having problems getting the 'ByAccount' aggregation to work in SQL Server Analysis Services (SSAS) 2008. I am trying to create a financial reporting solution, with Balance Sheet and Profit and Loss elements. I have a fact table with figures at a month level of granularity. There is one measure I am interested in on this table ...

Show Total In SSRS Chart

I am creating a line chart from an Analysis Services cube, with a date category, a count data field, and a status series. Here's a screenshot to explain: picasaweb.google.com/lh/photo/fP16V4sB18O1xSTrdDV-_A?authkey=Gv1sRgCLHRmcjwtI2mzAE I want to add the blue total line, which sums all the statuses together. I don't want to add a "T...

Connection between web parts in SharePoint

I have a couple of questions regarding SharePoint 2007. Is it possible to bind a List View Web Part to SQL Analysis Services using SharePoint configuration? I have two webparts. When clicking one row of a List View Web Part, I want to show a chart in another web part based on the value on the clicked row. How can I achieve this functio...

Fiscal Year in Excel When Browsing SSAS Cube

Hi I am using Excel as the front end to a SQL Server Analysis Services (SSAS 2008) cube. I have a "calendar" dimension, which consists of a hierarchy of year-quarter-period where period is a 4 or 5 week month. Excel offers lots of useful options under its "Date Filters" menu such as being able to select just Quarter 1 etc. This works ...

Handling nulls in Datawarehouse

Hi all, I'd like to ask your input on what the best practice is for handling null or empty data values when it pertains to data warehousing and SSIS/SSAS. I have several fact and dimension tables that contain null values in different rows. Specifics: 1) What is the best way to handle null date/times values? Should I make a 'default' ...

Excel Pivot Table Row Labels Not Refreshing

To all, I have an Excel spreadsheet pivot table that is linked to an SSAS data source. The employee counts are aggregated and return the following values: Row Label Count North America 7 South America 2 Europe 5 Asia 1 Australia 3 Let's say the user modifies the South America Row to read ...

Can an OLAP cube be constructed where the details of the model are not known in advance?

Pardon me for the woolly question - I'm not really that familiar with OLAP & cubes. Let me explain my situation... I'd like to build a database to store questionnaire results, where there might be a few dozen questions per questionnaire. Having gathered a few thousand completed questionnaires, I'd like to analyze the results, and that s...

How do I show graphs and KPIs from an OLAP cube in Sharepoint?

I need to display content from an OLAP cube in Sharepoint. This could be done using PerformancePoint Server but unfortunately Microsoft has decided to drop support for that product some time ago. Their plan is to integrate some of the functionality directly into the next version of Sharepoint instead. The problem is that I can't wait tha...

What type of Algorithm would you use to help forecast future performance based on historical data?

In the past we have been using a summary of historical performance by "Vendor" to decide how we allocate new business to each vendor. Now what we want to do is to break that summary performance into smaller subsets such geography, size, age, etc to better allocate the new business we hand out to our vendors. For example, Lets say that ...

What are the best resources for learning how to implement Naive Bayes Classifiers in SSAS?

After asking this question, I've decided to try and implement some Naive Bayes Classifiers using SQL Server Analysis Services. Can anyone point me to a decent book, website or any other resource on how to implement Naive Bayes Classifiers in SSAS? Similarly, I would be interested in learning about Decision Trees. ...

How do I limit SSAS hierarchy levels to users?

I am relatively new to ssas and am having trouble with something. The scenario: A cube with a company hierarchy (region, sub-region, country, company) Dimension security is applied by filtering the company dimension by linking username to a list of allowable companies. Enable Visual Total is switched ON so that you can only see totals...

What is the best approach to get from relational OLTP database to OLAP cube?

I have a fairly standard OLTP normalised database and I have realised that I need to do some complex queries, averages, standard deviations across different dimensions in the data. So I have turned to SSAS and the creation of OLAP cubes. However to create the cubes I believe my data source structure needs to be in a 'star' or 'snowflak...

Create test cube based on existing cube data (but much larger)

Is it possible to create a large cube based on existing cube data? We'd like to test the performance of certain tools in combination with SSAS and currently do not have any cubes large enough. e.g. We have a year's worth of data and want to expand it to be 10 year's worth ...

fact tables (Analysis Services)

I was just wondering if there could be any fact table, the keys of which dont belong to any of the dim tables? However, the fact table seems to contain the dim data. The reason I came up with this question is that I was looking into a package which uses a dim table and fact table to pull data from, manipulate and them dump into the fac...

Dimensional Modeling: should a fact table have a foreign key?

Can a fact table have no keys at all? or if it can, is it a good design? If a fact table do not have any dimensions, on what basis is it analyzed? What if a fact table has primary key/s only and no foreign key/s? ...

OLAP and SQL Server Analysis Services

Could you recommend some tutorials regarding OLAP cubes (especially in the context of Microsoft Project Server) and SQL Server Analysis Services? Books would also be great. ...

SQL Server Metrics

Hello, We are using SQL Server as our database server. We've built a consumer product and are looking to build a dashboard that has metrics on our user base. We're a team of two and were wondering if SSAS or if TSQL was the way to go to get metrics. Caveats We're not up to speed on SSAS so there would be a learning curve The data n...