Analysis :: Creating Tabular Model On OLTP
Nov 4, 2015
I have been looking at implementing a tabular model based on an OLTP database that's not dimensional. I know that this is possible but during my proof of concept I have encountered numerous problems ...
The things that I have run into are: After setting up the relationships I have found that measures filter context don't propagate along the relationships as I would expect. if the measure is coming from a target table and not a source then an ALL member is returned ( as in multi dimensional when a dimension isn't related to a measure group). Given the lay out of an OLTP database this will be hard to avoid.
One thing I have done to try an mitigate the above problem is to combine the tables used for measures in a view and using that in the source to connect to the rest of the tables. however due to the tables being of different grains this has then created duplication in some of the keys and measures. so the keys cant be used in relationships and the measures aren't accurate.
Are these things other people have come across? or should I give up the ghost and just recommend using dimensional models for the source? is tabular just geared towards a DW the same as multidimensional?
View 2 Replies
ADVERTISEMENT
Jul 2, 2013
I have a Tabular model, it has 8 tables , 7 of the tables process in 3 mins, but the main table is 22.3gb in size//I am processing the table on a 64 bit pc with 8 gb ram to the local instance from a Production server//At this rate it will take days to process the Main table 22.3gb, the main table was running off a view but I have inserted the data into TableA and updated the model///Only other optimization process I can think of is copying TableA to my local machine but the performance to the Server on the network is not an issue.I am just process the main table for the last 4 hrs and it is only at 11.7 mill records out of 124 mill total @ this rate it will take 44 hours
Size: 22342752 KB
CREATE
TABLE [dbo].[StagingTablevwKeyEventsfact](
[PKID] [bigint]
NULL,
[ManufactureGeoLocationCode]
[nvarchar](14)
NULL,
[code]....
View 6 Replies
View Related
Sep 25, 2015
We have a requirement to convert Tabular model to Multidimensional as few functionalists like actions does not support in tabular. Looking for list of things needs to take care to convert tabular solution to Multidimensional.
View 2 Replies
View Related
Oct 22, 2015
I'm using SQL Server 2012 Bi edition.
I created a model in Visual Studio 2012 and when I process the model, most tables (the ones that should) process 77,546 records and that is reflected in VS.
However, when I deploy the model to the server it is only deploying 76,500 records for those tables.
Where those 1,046 records went to? Is there a setting that limits the records to be deployed? My base tables in the Data Mart have 77,546 records, just as in my Visual Studio model.
View 3 Replies
View Related
Oct 18, 2012
how can i automatically update my tabular model in the future when there's an update in my database.
View 4 Replies
View Related
Jul 11, 2013
how to set 'Ignore Unrelated Dimensions' property in Tabular Model.
View 3 Replies
View Related
Nov 4, 2015
I get the following error while processing a SSAS tabular model (2014) on a new server.The SSAS service on this server is running under a login which has access to the SQL server data sources. I tried changing the provider to OLEDB from SQLCLNI11 in the connection string but that doesn't work too. The error message isn't useful to debug further.
The cube processing succeeds on a different server. I scripted out the cube DB and ran it on the new server and am trying to process full but it fails with the following error.
Error Message:
The operation failed because the source database does not exist, the source table does not exist, or because you do not have access to the data source.
More Details:
OLE DB or ODBC error: A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections.
For more information see SQL Server Books Online.; 08001; SSL Provider: No credentials are available in the security package
; 08001; Client unable to establish connection; 08001; Encryption not supported on the client.; 08001.
A connection could not be made to the data source with the DataSourceID of 'd7a37dae-be87-44e0-a8b2-498069af82c9', Name of 'connection name'.
An error occurred while processing the partition 'XXXX_460f3467-1a99-4dc9-aaf2-bcf3d54a5c4c' in table 'XXXX_460f3467-1a99-4dc9-aaf2-bcf3d54a5c4c'.
The current operation was cancelled because another operation in the transaction failed. The operation failed because the source database does not exist, the source table does not exist, or because you do not have access to the data source.
View 3 Replies
View Related
Jun 2, 2015
We are working on implementing DAX formula(s) on our tabular model (SSAS 2012). When we tried to deploy the solution, deployment wizard giving us then below error:The given key was not present in the dictionary...I checked all the formula(s) that we recently implemented and all looks fine.
View 4 Replies
View Related
Jun 27, 2015
I'm working on a Tabular model at my job since the users have a requirement to query against a live data source. I've been looking online for clients that issue DAX as opposed to MDX to the model, but besides SSRS and Power View, I could not find anything else. Any other client side interfaces to a DirectQuery Tabular model?
View 2 Replies
View Related
Jul 6, 2015
I built my first tabular model and see that my fact tables are also appearing as dimensions. In Multi dimensional mode i could choose which are the dimensions. How do i do that in tabular model.
View 2 Replies
View Related
Apr 17, 2015
I have a separate date dimension marked as Date table in Tabular Model and having proper relationship with another table(e.g SalesTable) with column of date type. But still time intelligence functions are not working. I am using the date column from other table (e.g SalesTable) in the formula. What exactly going wrong in our tabular model.
View 3 Replies
View Related
Jul 9, 2015
Question: Is it feasible to use a star schema dimensional model for an OLTP system that incurs few (750 per day)Sales Orders transactions?
Background: My customer wants to replace an existing OLTP system database because it runs on Oracle and their in-house expertise is in SQL Server. The original database developers that designed the Oracle DB have apparently retired. The Oracle database has been over-normalized, to say the least. The number of sales orders being entered daily is small: about 500-750 per day. These entries are done at the five clerks' convenience, from a paper form, and are very unlikely to ever be entered in quick succession. Nothing else gets regularly entered into this database except for the occasional change to a customer, but new customers are very few and far between.
I've designed a star schema for the replacement database with the Sales Order Header and Sales Order detail table combined into a single 'fact' table, and I've introduced some duplication into dimension tables (like customer) in order to eliminate some of the joins (and confusion) that were built into the original database.
I've never tried this before. Is there any reason this would not or should not work?
View 5 Replies
View Related
Nov 24, 2014
How to write Stored Procedures to load the Data Model from OLTP to DWH ?
View 9 Replies
View Related
Jun 17, 2014
I have a question about partitions in both SQL Server table and Tabular Model. I started to use Tabular Model recently.
I need to partition a table that collects daily rows for different clients.
The natural partition key is a combination of clientID+dateID (something like CL-YYYYMMDD)
I created a configuration table with a primary key PartitionID IDENTITY(1,1) , that contains also the field clientID and dateID
Every day I add a new row in it and I get a partitionID for the new client and date
Then I created a partitioned fact table using PartitionID as the partition field, using the partition function and the partition schema as well.
The daily client data is inserted in the partitioned table using the partitionID
Everything works fine, and the data are loaded correctly into the partitioned fact table.
Then I created a Tabular Model where the fact table is the partitioned table, and I created tabular model partitions using something like "select <field list> from PartitionedTable where partitionID = <partitionID>"
In this way, every day I load partitioned data in both sql server and tabular model. I have two dimensions, client and calendar
Now my question is: when I browse the Tabular Model, and I'm selecting a specific dimension date and dimension client, am I using the partitionID index correctly?
Or should I put in the tabular model partition query something like "select <field list> from PartitionedTable where clientID = <clientID> and dateID = <dateID>"? In this case is still working the partitionID index? How can I check it?
View 0 Replies
View Related
Jul 3, 2007
Hello,Can I import an OLTP (Reltional DB) as a Data Source into SQL ServerAnalysis Services 2005 and then use the Cube Wizard and the new DataSource View feature to create the OLAP model ?Or do I have to first design an OLAP Data Warehouse with a Star Schemaand then import this DW as a Data Source into my Analysis ServicesProject.With SQL Server 2000 , OLAP would be the way to go..but with SQLServer 2005 , it seems as though the wizard and data source viewfeatures do half the work for you.I have an OLTP DB and am not sure which route I should take ! Anysuggestions / input would be much appreciated.Thanks in Advance...RegardsRusszee
View 1 Replies
View Related
Mar 29, 2015
I am trying to learn building SSAS tabular model. While following a tutorial I need to add a column to an existing table but for some reason the ADD Column option (insert column in other menu is also not appearing) is greyed out.
View 4 Replies
View Related
Aug 10, 2015
i have a model contains fact of account revenue , a time dimension and scd of account.
the scd (type 2) is changing when an account get a new color.
when i queries the model by PowerPivot i sometimes get wrong color for an account in a date priod , for example , I would expect to see color 3 for account 1 at 04-06-2010 , But instead I see color 1 - as you can see in the picture bellow.
FACT , SCD :
Result
i process all , deploy , mark dim_time as date dim , and i still cant find the error.
View 3 Replies
View Related
Oct 5, 2015
I have been living in the T-SQL world for years, and am working on my first Tabular model.
I have a fact table that I would like to use for many reports. My idea is that I would use Perspectives in the tabular model to pull in the columns that I need for each purpose. In some cases, I also need to filter out some rows. Is there a way to filter perspectives? The only solution I am coming up with is to create views in SQL Server and import the fact table multiple times for each purpose. That doesn't seem very efficient.
View 2 Replies
View Related
Jun 18, 2015
My boss and I attended the SQL conference in Vegas a few years ago. He remembers a session on tabular where the presenter said when the underlying table that tabular is built on changes, it automatically refreshes the tabular db. Now that we're working on a tabular project I'm looking around for this information and don't find anything like that happens. From what I can see, the refresh either has to be scheduled from a task, or manually refreshed by right-clicking the db and selecting "process db". He said the session presenter was a Microsoft Program Manager too. Is there any truth to what he remembers?
View 2 Replies
View Related
Sep 2, 2015
According to the book "Microsoft SQL Server 2012 Analysis Services - The BISM Tabular Model" (pages 276 and 277) the following DAX query should work. However, I'll get a rank of 1 returned for every product name. My impression is that ALL doesn't block the context transition for product name. Because there is nothing in the errata, I might do something wrong!? The second query is just a workaround giving the correct result.
EVALUATE
CALCULATETABLE(
SUMMARIZE(
'Internet Sales',
Product[Product Name],
[code]....
View 2 Replies
View Related
Aug 3, 2015
SQL 2014...In SSDT, as I'm developing my tabular model I often add new columns to the underlying table/view that feeds my model. When I go into Table/Table Properties, select the new column I've added then hit OK it spins and spins then times out. My model has just over 1 million records. The only way I can get it to work is to delete the table and then add it in again.
View 2 Replies
View Related
Jun 16, 2015
how to combine measures and fields coming from an analytical model (tabular) along with some Excel calculations. Basically I want to provide users with a simple report (to be displayed in SharePoint Excel services) containing charts and slicers. The data comes from a tabular model, and most of the calculations are in the model as well.However there is some little tweaking that must be done. For example I might need additional calculated columns, but I don't feel the need to modify the tabular model for that. I was wondering if I could do this within Excel as well -- but without having to bring all the data through a pivot table, then manipulate it and then show it on the report. So to be clear I do not want any pivot tables lying around, even if on a hidden sheet.
I noticed that when selecting a pivot chart in Excel, at the ribbon menu under "PIVOTCHART TOOLS"/"ANALYSE" there is a group of buttons named "Calculations". One of them is named OLAP Tools.Is it fair to assume that these options will allow me to create new measures at the Excel side, without affecting my tabular model?
View 2 Replies
View Related
Nov 4, 2015
I tried using the page below but wasn't able to quite properly adapt it to my scenario
[URL]
I have two UNRELATED (can't be related for reasons outside the scope of this, other relationships already exist) tables.
Each table does, however, contain a WorkerID:
WorkerTimesheets:
WorkerID, HoursLogged, Date
3,10,2015-05-05
3,6,2015-05-10
4,8,2015-05-01
Then the unrelated table "ConstructionSites"
SiteID, SiteManagerID, SiteOpen,SiteClosed
A5, 3,2015-01-01,BLANK
What I want to do is be able to show a measure that SUMS the number of hours logged by anyone who is a SiteManager from the ConstructionSites table.
I wanted to do a SUMX of WorkerTimesheets against HoursLogged, but FILTER against WorkerTimesheets[WorkerID] = ConstructionSites[SiteManagerID] so only workers who are also SiteManager would be counted.However, I can't seem to get that to resolve it always throws an error along the lines that it can't determine context.
View 3 Replies
View Related
Aug 1, 2012
SQL Server Data Tools within VS 2010 Shell.2012 SASS in Tabular Mode.I've created a Measure and KPI in my fact table. I've created a perspective named "Summary" When selecting the fields and measures for display in the perspective, the measure is available, but not the KPI (target, status, etc.). When Analyzing in Excel with the Summary perspective, the KPI is not available in the field list. When analyzing in Excel with the (default) perspective, the KPI is available.As far as I can tell, there is no way to select a KPI within the perspective window.
View 8 Replies
View Related
May 12, 2015
getting a complete Microsoft DW/BI (Tabular), Power BI and Office enviroment up running. I am a (Microsoft) DW/BI manager and want to learn about the latest Microsoft DW/BI (SQL Server Tabular) and Power BI (Power Pivot, Power View, ...). To save time & access from anywhere, I thought I could use Windows Azure but quickly got lost in the Azure preview and market place! I even contacted the Windows Azure team and that didn't clarify things.
So, how to get a SQL Server (2014) environment with all DW/BI (SSIS, SSAS, SSRS, Data Mining) technologies together with Power BI and Excel working? If so, how to get started.If this is not possible on Azure and it has to be done on Window, what do I need to install for the Power BI stuff (to work with Tabular)? I already have SQL Server 2014 and Office Pro Plus installed natively on my Windows machine.
View 6 Replies
View Related
Nov 13, 2015
I have a new 2014 SSAS installation. During VS2013 Tabular Project create I get an error Cannot connect, Reason The workspace database on server ***** is not running in tabular mode.I've changed msmdsrv.ini - DeploymentMode>2...The server wont start.
View 3 Replies
View Related
Jul 16, 2015
I am working with SSAS Tabular. I have a stand alone table with 60 columns and contains 120K records. Table size is 250MB. And trying to build a tabular report out of it and it is taking longer and throwing exception, screenshot attached.
It might be cross-join issues, as workaround created a dummy measure and using in report. But it working for 10-20 k records and beyond throwing same exception. I have 8 GB RAM and 100 GB free disk space.
Customer wanted it in SSAS Tabular...
View 3 Replies
View Related
Oct 1, 2015
i would like to pose a question, when a user do a login in SSRS server,is it possible to pass the username to SSAS tabular, to implement security? I've tested using USERNAME() creating a role that relates USERNAME() with a dim user username, but now i need to implement it with the username used in SSRS server.
View 2 Replies
View Related
Oct 30, 2015
Does any1 know why preview of a DAX calculation does not work? I mean once you enter the calculation in the bottom of the table it doesnt´t show up.
View 8 Replies
View Related
Nov 10, 2015
I have a Tabular data model and I'm returning a measure that counts employees (each row is an employee) and then a calculated column in the model that gets SeniorityInMonths.
So if an employee was hired exactly 1 year ago, they would have 12 in this column.
I want to group these into bins, but the Group option is grayed out.
View 6 Replies
View Related
Aug 25, 2015
We couldn't find a way to show row/column members in the order as in the database. The Pivot table always applies alphabetical sorting:
Is there really no way to show items as per database order?
View 4 Replies
View Related
May 14, 2015
One of my models has order data, cost per order/invoice ID and then dimensions on Fiscal Year, category, etc...the usual.
A user wanted to search it for an exact order amount. (They knew for example that one of our accounts was not balancing by single order worth $746.13 and assumed it must be an order that was placed but never marked shipped that slipped through the cracks).
Now, in the model I have "order amount" as a field and then a measure that sums that.
I could expose that "order amount" field as a label and let them filter on it in Excel (and that works).
However, I haven't had any luck filtering on the actual measure "Total Order Amount". Such as OrderID-> View Filter -> "Total Order Amount" equals 746.13.
I assume this is due to a few things:
Measure calculates at different levels so filtering on a measure is difficult as you would have to place all the "slicers" and set them first before the measure would "exist" at a level where it could be $746.13. Orders by year would have $746.13 as part of it's year sum, but wouldn't exist as a stand alone line item orders by year 2015 might be 2 million.
Orders by category might exist at 500,000, 8,000, 15,146.36, etc... but not $746.13.
So I would need OrderID on there as a column so the measure could return at the value of $746.13 for one row for it to match the filter?
Basically:
1. Why it can't really filter on a measure?
2. Is there a better way to accomplish this other than exposing the actual column in the fact table "order amount" as it feels like that could cause all kinds of confusion if other users try to slice/filter on that not realizing exactly what it is meant to be?
View 3 Replies
View Related
Aug 6, 2015
It seems simple but I'm not able to make it works. I've got a fact table with sites (DimImplantation) with allowed users (DimDroit) :
1 user can have access to many sites. I've tried these codes but only one worked and only in case where the user had only one site :
a) =DimImplantation[DimImplantation_ID]= LOOKUPVALUE (DimDroit[DimImplantation_ID]; DimDroit[Utilisateur];USERNAME() ) ==> worked for 1user with 1 site
b) =DimImplantation[DimImplantation_ID]= LOOKUPVALUE (DimDroit[DimImplantation_ID]; DimDroit[Utilisateur];USERNAME(); DimDroit[DimImplantation_ID];DimImplantation[DimImplantation_ID] ) ==> doesn't work and says 'not able to to identifie the value of DimImplantation[DimImplantation_ID]
in the current context.
c) CALCULATE(VALUES(DimDroit[DimImplantation_ID], SUMMARIZE('DimDroit',DimDroit[Utilisateur]), DimDroit[Utilisateur]=USERNAME()) ==> version for SQL2012. I've had 'End of input reached'.
View 3 Replies
View Related
